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

jaadu

New member
Joined
Nov 13, 2011
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
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.
 

Attachments

  • Book1.xlsx
    10.2 KB · Views: 86

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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))
 

jaadu

New member
Joined
Nov 13, 2011
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Dd you array-enter it as I stated?
 

jaadu

New member
Joined
Nov 13, 2011
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
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.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
220
Reaction score
0
Points
16
Location
UK
Excel Version(s)
2016
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))
 

jaadu

New member
Joined
Nov 13, 2011
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
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.
 

jaadu

New member
Joined
Nov 13, 2011
Messages
13
Reaction score
0
Points
0
Excel Version(s)
Excel 2010
Joe - just to make you aware, I got the MIN formula to work, so please ignore in my previous message.
 
Top