Dynamic Average using MS Excel 2007

I've named Data Table as Table1. See Figure 1.0. If you are well familiar to the functionality of MS Excel 2007. This data needs to update on daily bases unless and untill there isn't any holiday.

Now, based on that data I need to identify the historical Maximum figure, Minimum figure along with the average of the particular period.

Following formula is basically to find out the maximum value in 1 month. Similarly, you can use the same one for Minimum value and Maximum Value by replacing "Average" to "Min" and "Max".

=AVERAGE(OFFSET(Table1[[#Headers],[Date]],MATCH(DATEVALUE(Date),Table1[Date],0),1):OFFSET(Table1[[#Headers],[Date]],MATCH(EDATE(OFFSET(Table1[[#Headers],[Date]],MATCH(DATEVALUE(Date),Table1[Date],0),0),-1),Table1[Date],1),1))


DATEVALUE(Date): I've defined the name "Date" to the cell of required date.


[Date]: It's the reference to coloumn of Date in Table1 which you can see in Figure 1.0
-1: In the last row you'll find this which basically represents Month. If you want to find out the average, maximum, or minimum value in last 3 months then replace "-1" to "-3"


Feel free to write your feedback.