# Formula for annual average from monthly data

H

#### hello

Hello

I have a spreadsheet with many columns of monthly data spanning many (let's
say 50) years. I want to write a formula that shows the annual averages of
each column of data. It is easy enough to get the average by simply typing
=AVERAGE(B1:B13), but it is tedious to copy this formula, and then modify the
starting and ending rows to average the data for the next year, especially
because once you know the cell that is January of the first year, you know
the range over which to take the average for every year (add eleven to get
the final row of the first year, add one to get the first row of the next

Is there a way to write a formula that would create the correct cell
references? I have experimented with CONCATENATE and INDIRECT, but to no
avail.

Thank you very much

=AVERAGE(INDEX(B:B,(ROW(A1)-1)*12+1):INDEX(B:B,ROW(A1)*12-1))

My solution:
=AVERAGE(OFFSET(INDIRECT("B"&ROW()-12),0,0,12,1))

Regards,
Stefi

â€žhelloâ€ ezt Ã­rta:

Bob -- This appears to work, and it is a good way to learn more about how to
use INDEX

Thanks!

Stefi said:
My solution:
=AVERAGE(OFFSET(INDIRECT("B"&ROW()-12),0,0,12,1))

Regards,
Stefi

â€žhelloâ€ ezt Ã­rta: