Formula for annual average from monthly data
 

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

Formula for annual average from monthly data
 

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

--
__________________________________
HTH

Bob

"hello" wrote in message
...
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

Formula for annual average from monthly data
 

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

Regards,
Stefi

€žhello€� ezt Ã*rta:

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

Formula for annual average from monthly data
 

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

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"hello" wrote in message
...
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

Formula for annual average from monthly data
 

Thanks!

"Stefi" wrote:

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

Regards,
Stefi

€žhello€� ezt Ã*rta:

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