Rolling Average of 3 columns and 5 rows

[Claire G]

I am trying to find a formula that will provide a rolling average of 3
months for columns B, C & D. The formula that I have below works
perfectly for column B only ... I don't know how to change the formula
to include columns C & D.

A B C D
Dec 27.53 27.61 27.43
Jan 27.55 27.77 26.65
Feb 28.35 28.6 28.44
Mar 27.14 27.74 28.02
Apr 28.67 28.04 28.09

3 28.05

=AVERAGE(OFFSET(B1,COUNT(B:B)-1,0,-A7))

 

[Art Farrell]

Hi Claire,

This isn't using your COUNT formula but a combination of Index and Match to
pick up the range. Put the month you want in F1, such as Jan.
Then use this formula to give you the average of Jan thru Mar for the four
columns:

=AVERAGE(OFFSET(INDEX(A1100,MATCH(F1,A1:A100,0),1),0,1):INDEX(A1100,MATC
H(F1,A1:A100,0)+2,4))

The +2 at the end of the second match moves it down two rows and the 4 makes
it move to column D.

CHORDially,
Art Farrell

 

[Claire G]

Art,

You are a life saver. Thanks very much. The formula is perfect.

Claire