Excel 2007 : Share price workbook

Hi guys,

I have some share pricing data (please see attached workbook)

My goal is to have a formula for B6 so that it looks up the maximum price in column K based on the Pricing Date (column I) which is based on the Period (A6). In other words, I would be able to change A6 from June 2010 to say July 2010, and B6 would automatically look up the max price in K26:K47 instead of me having to manually go through and alter the range from K4:K25 each time I want to change the Period month.

Any help would be much appreciated!

Thanks

Book2.xlsx

Hello
There are a few ways to do this using filters, tables or Database functions but if you want a single formula try the following in cell B6, assuming A6 is a valid date.

=MAX((MONTH(Pricing_Date)=MONTH(A6))*(YEAR(Pricing_Date)=YEAR(A6))*(DAY_HIGH_PRICE))

I've named your database fields for ease of understanding. Also this is an Array formula and must be entered with CTRL+SHIFT+ENTER.

Hope this helps.
DBY

Hi DBY,

Thanks for your help, that formula works great.

I tried doing similar for MIN instead of MAX using the following formula however it's returning a value of 0.000:

=MIN((MONTH(Pricing_Date)=MONTH(A6))*(YEAR(Pricing_Date)=YEAR(A6))*Day_Low_Price)

i.e.

=MIN((MONTH(I4:I269)=MONTH(A6))*(YEAR(I4:I269)=YEAR(A6))*L4:L269)

which I entered in as an array formula.

Would this formula work for MIN, or am I doing something wrong?

Thanks again, much appreciated

Glad it worked. Yes, it's a little more tricky with the Min function because my current formula returns a series of zeros, which of course the Max function ignores, the Min however, does not. So you will need to amend the formula:

=MIN(IF((MONTH(Pricing_Date)=MONTH(A6))*(YEAR(Pricing_Date)=YEAR(A6))>0,DAY_LOW_PRICE))

Once again an Array formula entered CTRL+SHIFT+ENTER.

But as I said, if you don't want to use Array formulas, you could use the various Database functions with criteria to return your values.

Hope this helps.
DBY