find max and min value for past 30 days, by changing date

hi - i have attached a spreadsheet in which I am trying to figure out the max and min value for the data set, by changing the date in cell A1.

so basically, if I enter Feb 17, 2011 in cell A1, it should help identify the max and min for the past 30 days (most likely until Jan 17, 2011), and if I change the date to Feb 18, 2011, then the max and min should go back to Jan 18, 2011.

can someone help me generate a formula for both the max and min?

feel free to post your suggestion here in the message board or in the attached excel spreadsheet.

thank you in advance for your time and any help you can provide will be greatly appreciated.

TRy these array formulae (commit with Ctrl-Shift-Enter)

=MAX(IF(($A$9:$A$100>=$A$1-30)*($A$9:$A$100<=TODAY()),$B$9:$B$100))

=MIN(IF(($A$9:$A$100>=$A$1-30)*($A$9:$A$100<=TODAY()),$B$9:$B$100))

Mr. Phillips - thank you for your reply. I tried your formula, and for some reason, I kept getting the same value for max, regardless of what date I put in cell A1.

I had the chance to think about how to solve this a little more, and I came up with the following, but, I still can not make it work correctly.

=INDEX(B9:B50000,MATCH(A1,A9:A50000,1))

Do you think the above formula can work with a little adjusting, as it does not have the max/min function and anything to indicate -30 (data for the past 30 days).

Thanks again.

Dd you array-enter it as I stated?

yes, i entered the formula with ctrl+shift+enter, but the min and max formulae do not seem to return the correct min and max value.

For example, if I enter Feb 1, 2011 in cell A1, and enter your.....

max formula in cell B3, I get 24.2, but that is not correct, as the max is 22.80 which occured on Jan 4, 2011 (maximum value within the past 30 days from Feb 1, 2011).

min formula in cell B4, I get 21.45, but that is not correct, as the min is 21.00 which occured on Jan 17, 2011 (minimum value within the past 30 days from Feb 1, 2011).

hopefully I am not missing anything, but if I am, sorry for the trouble.

I do appreciate your time and look forward to hearing from you.

I believe you need to alter the TODAY() part to use A1 too:

=MAX(IF(($A$9:$A$100>=$A$1-30)*($A$9:$A$100<=$A$1),$B$9:$B$100))

Joe - Thanks for the correction.

Also, I was reviewing the formula, and can you help explain what the following means (portion indicated in red):

=MAX(IF(($A$9:$A$100>=$A$1-30)*($A$9:$A$100<=$A$1),$B$9:$B$100))

Also, I tried to tweek this formula for MIN, but I could not get it to work. Can you post your comment for the MIN formula also?

Thank you once again for all your help.

Joe - just to make you aware, I got the MIN formula to work, so please ignore in my previous message.