I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam

On Sat, 14 Feb 2009 06:26:01 -0800, Stibbz
wrote:

I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam


Where do you want your averages to be placed on the worksheet?

Lars-Åke

If your data are in one column downward and every time you want to calculate
the average of 12 rows, then simply click the cell for first average you have
calculated, copy the cell go 12 rows down and paste it.
This will copy the formula and also shifts the cell reference 12 rows down.

--
R. Khoshravan
Please click "Yes" if it is helpful.


"Stibbz" wrote:

I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam

I want my averages to be placed one below each other from cells G2 through to
G146

"Lars-Ã…ke Aspelin" wrote:

On Sat, 14 Feb 2009 06:26:01 -0800, Stibbz
wrote:

I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam


Where do you want your averages to be placed on the worksheet?

Lars-Ã…ke

On Sat, 14 Feb 2009 06:47:01 -0800, Stibbz
wrote:

I want my averages to be placed one below each other from cells G2 through to
G146

"Lars-Åke Aspelin" wrote:

On Sat, 14 Feb 2009 06:26:01 -0800, Stibbz
wrote:

I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam


Where do you want your averages to be placed on the worksheet?

Lars-Åke


So if I understand you correctly,
in cell G2 you want the average of C2:C13
in cell G3 you want the average of C14:C25
in cell G4 you want the average of C26:C37
and so on until
in cell G146 you want the average of C1730:C1741

Try the following cell in cell G2 and copy it down to G146

=AVERAGE(OFFSET(C$2,12*(ROW()-2),,12))

Hope this helps / Lars-Åke

Haha it works like a treat!
Thank you very much for your help

Sam

"Lars-Ã…ke Aspelin" wrote:

On Sat, 14 Feb 2009 06:47:01 -0800, Stibbz
wrote:

I want my averages to be placed one below each other from cells G2 through to
G146

"Lars-Ã…ke Aspelin" wrote:

On Sat, 14 Feb 2009 06:26:01 -0800, Stibbz
wrote:

I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam


Where do you want your averages to be placed on the worksheet?

Lars-Ã…ke


So if I understand you correctly,
in cell G2 you want the average of C2:C13
in cell G3 you want the average of C14:C25
in cell G4 you want the average of C26:C37
and so on until
in cell G146 you want the average of C1730:C1741

Try the following cell in cell G2 and copy it down to G146

=AVERAGE(OFFSET(C$2,12*(ROW()-2),,12))

Hope this helps / Lars-Ã…ke

Hi,

You can do it with a number of formulas:

=AVERAGE(INDIRECT("C"&12*ROW()-11&":C"&12*ROW()))

=AVERAGE(OFFSET(C$1,12*ROW()-12,,12))


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Stibbz" wrote:

I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam

Try this:

=AVERAGE(OFFSET(C$2,(ROWS(G$2:G2)-1)*12,,12))

Copy down as needed.

Using the ROW() function with an empty argument is vulnerable to row
insertions/deletions.

--
Biff
Microsoft Excel MVP


"Stibbz" wrote in message
...
I need to calculate the average temperature of every year and I know the
formula is '=AVERAGE(C2:C13)' but how would I replicate this so I don't
have
to type it into every cell to work it out. As the next one would be
'AVERAGE(C14:C25)

Any help would be greatly appreciated!

Thanks in advance
Sam