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
year, add eleven, etc).

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
 
Ad

Advertisements

S

Stefi

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

Regards,
Stefi

„hello†ezt írta:
 
H

hello

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top