1 year moving days Max and Min

[AlGuy]

I am looking for an Excel formula to display on a new cell the max and min values of the trailing year data, assuming the cells are always updated with new data.

Below is the example of the table. (Column B=Date, Column C=Time period, Column D=Index, Column E=Price).

Rows/Columns B C D E
1 Date Time period Index Price
2 2-Oct-98 1 1 22 000
3 5-Oct-98 2 1 22 000
4 7-Oct-98 3 1 23 000
5 9-Oct-98 4 1 22 000
6 12-Oct-98 5 1 23 000
7 14-Oct-98 6 1 23 000
8 16-Oct-98 7 1 22 000
9 19-Oct-98 8 1 23 000
10 21-Oct-98 9 1 22 000
11 23-Oct-98 10 1 22 000
12 26-Oct-98 11 1 22 000
13 28-Oct-98 12 1 22 900
14 30-Oct-98 13 1 22 000
15 2-Nov-98 14 1 22 000
16 4-Nov-98 15 1 22 900
17 6-Nov-98 16 1 22 900
18 9-Nov-98 17 1 22 000
19
20

Assuming more data will be posted on rows 19,20,21... and that today's date is March 2 2012, I would like a formula which will display the max and min price (column E) between now and March 2 2011 without manually adjusting the date and time frame every time data are entered.

Thank you.

 

[Aladin Akyurek]

I am looking for an Excel formula to display on a new cell the max and min values of the trailing year data, assuming the cells are always updated with new data.

Below is the example of the table. (Column B=Date, Column C=Time period, Column D=Index, Column E=Price).

Rows/Columns B C D E
1 Date Time period Index Price
2 2-Oct-98 1 1 22 000
3 5-Oct-98 2 1 22 000
4 7-Oct-98 3 1 23 000
5 9-Oct-98 4 1 22 000
6 12-Oct-98 5 1 23 000
7 14-Oct-98 6 1 23 000
8 16-Oct-98 7 1 22 000
9 19-Oct-98 8 1 23 000
10 21-Oct-98 9 1 22 000
11 23-Oct-98 10 1 22 000
12 26-Oct-98 11 1 22 000
13 28-Oct-98 12 1 22 900
14 30-Oct-98 13 1 22 000
15 2-Nov-98 14 1 22 000
16 4-Nov-98 15 1 22 900
17 6-Nov-98 16 1 22 900
18 9-Nov-98 17 1 22 000
19
20

Assuming more data will be posted on rows 19,20,21... and that today's date is March 2 2012, I would like a formula which will display the max and min price (column E) between now and March 2 2011 without manually adjusting the date and time frame every time data are entered.

Thank you.

Try...

H2, just enter:

=MATCH(9.99999999999999E+307,A:A)

H3, control+shift+enter, not just enter:

=MIN(IF($A$2:INDEX(A:A,$H$2)>=EDATE(TODAY(),-12),$E$2:INDEX(E:E,$H$2)))

H4, control+shift+enter, not just enter:

=MAX(IF($A$2:INDEX(A:A,$H$2)>=EDATE(TODAY(),-12),$E$2:INDEX(E:E,$H$2)))

EDATE requires the Analysis Toolpak add-in on Excel 2003 and earlier.

 

[AlGuy]

Thank you Aladin,

Could you please tell me what the step H1 is supposed to achieve? The formula work just fine so far. I tested them and so far they are doing the job.
Thanks. But please, tell me what the step H1 is supposed to achieve.

 

[Aladin Akyurek]

Thank you Aladin,

You are welcome.

Could you please tell me what the step H1 is supposed to achieve? The formula work just fine so far. I tested them and so far they are doing the job.
Thanks. But please, tell me what the step H1 is supposed to achieve.

My reply does not list/mention H1. Do you mean H2?

 

[AlGuy]

Yes, H2. Sorry.

 

[Aladin Akyurek]

Yes, H2. Sorry.

H2 determines the last row of the ranges of interest. Its use in the INDEX bits makes the range specification dynamic. That is, whenever a range shrinks or grows, the formula knows that automatically.

Hope this helps.

 

[AlGuy]

Thank you. Your help and expertise are really helpful.

 

[Aladin Akyurek]

Thank you. Your help and expertise are really helpful.

You are welcome.