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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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