90 day average formula

[ebailey]

Version: Excel 2002

Need help with a formula that will provide a average for the last 90
days or the last three months.
(Whichever is easier)

Specs:

Rows B4 thru M4 are set as the first of the month for a calendar year
(Example: Aug-06)

Rows B5 thru M5 are the values we need a last 90 day average of.

Contained in Rows B5 thru M5 are blank cells

Any help would be appreciated

 

[Max]

One way ..

In B4:M4 are the "1st of month" headers: Jan-06, Feb-06, ... Dec-06
Data in row 5 down

Put in say, N5:
=AVERAGE(OFFSET(INDIRECT(CHAR(65+MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$B$4:$M$4,0))&4),ROW(A1),,,-3))
Copy N5 down

Col N will return the average for the last 3 months (inclusive current
month), eg: if today is within Jun-06, we'd get the averages for Apr-06
to Jun-06

If what's wanted is the average for the last 3 months (exclusive
current month), eg: if today is within Jun-06, we want the averages for
Mar-06 to May-06,
then just use instead in N5, and copy down:
=AVERAGE(OFFSET(INDIRECT(CHAR(65+MATCH(DATE(YEAR(TODAY()),MONTH(TODAY()),1),$B$4:$M$4,0)-1)&4),ROW(A1),,,-3))