Rolling Average Formula

[Sarah]

Hello

I have been trying to work out how to simplify my monthly
reporting so that I do not have to move formulas around
each month.

Is it possible to set up a formula to automatically
calculate a rolling average? For example, I would like to
enter the report month in one spot (ie, August-03) and the
formula automatically knows to sum the last 6 months worth
of data.

A1 = Report Month
A2:H2 = Month headers (Feb-03 through Sept-03)
A3:H3 = data
Essentially I want the formula to change like this...
If A1 = Jul-03,sum(A3:F3)
If A1 = Aug-03,sum(B3:G3)
If A1 = Sept-03,sum(C3:H3)
....and so on for the next 12 months.

Thank you for your assistance,
Sarah

 

[J.E. McGimpsey]

One way:

=SUM(OFFSET(A3,0,MONTH(A1)-7,1,6))