Hi
Ive been asked to work out the average temperature for each year from
1880-2007. So i have been doing this by adding the first 12 temp data and
then dividing by 12. But when i try to fill the the formula it doesnt
remember to add up the next set of 12. So i need to go from =sum(a1:a12)/12
and then change to =sum(a13:a25)/12 and then to =sum(a26:a38)/12 etc etc. If
anybody could help us with this it would save me loads of time
Cheers

Year Month Temp
1880 1 -5.2 17.7
1880 2 1.7 31.1
1880 3 7.6 14.5
1880 4 10.1 32.8
1880 5 13.2 11.5
1880 6 15.2 53.1
1880 7 19.1 42.1
1880 8 17.2 65.9
1880 9 14.6 56.8
1880 10 9.2 38.7
1880 11 2.9 64.5
1880 12 1.1 63.9
1881 1 -3.7 79.7
1881 2 1.6 33.8
1881 3 6 55.3
1881 4 9.8 16.7
1881 5 13.4 20.1
1881 6 16.8 16.1
1881 7 21 43.3
1881 8 17.9 129.8
1881 9 12.4 34.5
1881 10 5.7 34.4
1881 11 4.2 13.1
1881 12 -1.5 13.5

see my response to your other thread.

Also, you only need to post a question once. Please try to keep all
responses in one thread.




"Smalawi" wrote in message
...
Hi
Ive been asked to work out the average temperature for each year from
1880-2007. So i have been doing this by adding the first 12 temp data and
then dividing by 12. But when i try to fill the the formula it doesnt
remember to add up the next set of 12. So i need to go from
=sum(a1:a12)/12
and then change to =sum(a13:a25)/12 and then to =sum(a26:a38)/12 etc etc.
If
anybody could help us with this it would save me loads of time
Cheers

Year Month Temp
1880 1 -5.2 17.7
1880 2 1.7 31.1
1880 3 7.6 14.5
1880 4 10.1 32.8
1880 5 13.2 11.5
1880 6 15.2 53.1
1880 7 19.1 42.1
1880 8 17.2 65.9
1880 9 14.6 56.8
1880 10 9.2 38.7
1880 11 2.9 64.5
1880 12 1.1 63.9
1881 1 -3.7 79.7
1881 2 1.6 33.8
1881 3 6 55.3
1881 4 9.8 16.7
1881 5 13.4 20.1
1881 6 16.8 16.1
1881 7 21 43.3
1881 8 17.9 129.8
1881 9 12.4 34.5
1881 10 5.7 34.4
1881 11 4.2 13.1
1881 12 -1.5 13.5

Using a pivot table would be perfect in this situation. You can easily get a
list of the totals.

"Smalawi" wrote:

Hi
Ive been asked to work out the average temperature for each year from
1880-2007. So i have been doing this by adding the first 12 temp data and
then dividing by 12. But when i try to fill the the formula it doesnt
remember to add up the next set of 12. So i need to go from =sum(a1:a12)/12
and then change to =sum(a13:a25)/12 and then to =sum(a26:a38)/12 etc etc. If
anybody could help us with this it would save me loads of time
Cheers

Year Month Temp
1880 1 -5.2 17.7
1880 2 1.7 31.1
1880 3 7.6 14.5
1880 4 10.1 32.8
1880 5 13.2 11.5
1880 6 15.2 53.1
1880 7 19.1 42.1
1880 8 17.2 65.9
1880 9 14.6 56.8
1880 10 9.2 38.7
1880 11 2.9 64.5
1880 12 1.1 63.9
1881 1 -3.7 79.7
1881 2 1.6 33.8
1881 3 6 55.3
1881 4 9.8 16.7
1881 5 13.4 20.1
1881 6 16.8 16.1
1881 7 21 43.3
1881 8 17.9 129.8
1881 9 12.4 34.5
1881 10 5.7 34.4
1881 11 4.2 13.1
1881 12 -1.5 13.5

Assuming you want to average the temperatures in column D

=SUM(INDEX(D:D,ROW(D1)*12-11):INDEX(D:D,ROW(D1)*12))/12

Drag/copy down as far as you wish.


Gord Dibben MS Excel MVP


On Fri, 29 Feb 2008 15:06:01 -0800, Smalawi
wrote:

Hi
Ive been asked to work out the average temperature for each year from
1880-2007. So i have been doing this by adding the first 12 temp data and
then dividing by 12. But when i try to fill the the formula it doesnt
remember to add up the next set of 12. So i need to go from =sum(a1:a12)/12
and then change to =sum(a13:a25)/12 and then to =sum(a26:a38)/12 etc etc. If
anybody could help us with this it would save me loads of time
Cheers

Year Month Temp
1880 1 -5.2 17.7
1880 2 1.7 31.1
1880 3 7.6 14.5
1880 4 10.1 32.8
1880 5 13.2 11.5
1880 6 15.2 53.1
1880 7 19.1 42.1
1880 8 17.2 65.9
1880 9 14.6 56.8
1880 10 9.2 38.7
1880 11 2.9 64.5
1880 12 1.1 63.9
1881 1 -3.7 79.7
1881 2 1.6 33.8
1881 3 6 55.3
1881 4 9.8 16.7
1881 5 13.4 20.1
1881 6 16.8 16.1
1881 7 21 43.3
1881 8 17.9 129.8
1881 9 12.4 34.5
1881 10 5.7 34.4
1881 11 4.2 13.1
1881 12 -1.5 13.5

=AVERAGE(IF(A2:A1000=1880+ROWS($1:1)-1,D2:D1000))

Adjust your range to suit
ctrl+shift+enter, not just enter
copy down as far as needed


"Smalawi" wrote:

Hi
Ive been asked to work out the average temperature for each year from
1880-2007. So i have been doing this by adding the first 12 temp data and
then dividing by 12. But when i try to fill the the formula it doesnt
remember to add up the next set of 12. So i need to go from =sum(a1:a12)/12
and then change to =sum(a13:a25)/12 and then to =sum(a26:a38)/12 etc etc. If
anybody could help us with this it would save me loads of time
Cheers

Year Month Temp
1880 1 -5.2 17.7
1880 2 1.7 31.1
1880 3 7.6 14.5
1880 4 10.1 32.8
1880 5 13.2 11.5
1880 6 15.2 53.1
1880 7 19.1 42.1
1880 8 17.2 65.9
1880 9 14.6 56.8
1880 10 9.2 38.7
1880 11 2.9 64.5
1880 12 1.1 63.9
1881 1 -3.7 79.7
1881 2 1.6 33.8
1881 3 6 55.3
1881 4 9.8 16.7
1881 5 13.4 20.1
1881 6 16.8 16.1
1881 7 21 43.3
1881 8 17.9 129.8
1881 9 12.4 34.5
1881 10 5.7 34.4
1881 11 4.2 13.1
1881 12 -1.5 13.5

Add all of the temperatures and divide by the total number of temperatures
to get the average. It does not matter how many time periods you have.

Tyro

"Smalawi" wrote in message
...
Hi
Ive been asked to work out the average temperature for each year from
1880-2007. So i have been doing this by adding the first 12 temp data and
then dividing by 12. But when i try to fill the the formula it doesnt
remember to add up the next set of 12. So i need to go from
=sum(a1:a12)/12
and then change to =sum(a13:a25)/12 and then to =sum(a26:a38)/12 etc etc.
If
anybody could help us with this it would save me loads of time
Cheers

Year Month Temp
1880 1 -5.2 17.7
1880 2 1.7 31.1
1880 3 7.6 14.5
1880 4 10.1 32.8
1880 5 13.2 11.5
1880 6 15.2 53.1
1880 7 19.1 42.1
1880 8 17.2 65.9
1880 9 14.6 56.8
1880 10 9.2 38.7
1880 11 2.9 64.5
1880 12 1.1 63.9
1881 1 -3.7 79.7
1881 2 1.6 33.8
1881 3 6 55.3
1881 4 9.8 16.7
1881 5 13.4 20.1
1881 6 16.8 16.1
1881 7 21 43.3
1881 8 17.9 129.8
1881 9 12.4 34.5
1881 10 5.7 34.4
1881 11 4.2 13.1
1881 12 -1.5 13.5