HELP WITH EXCEL FORMULA

[Stibbz]

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

 

[Lars-Åke Aspelin]

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

 

[Khoshravan]

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.

 

[Stibbz]

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

 

[Lars-Åke Aspelin]

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

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

 

[Stibbz]

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

Sam

 

[Shane Devenshire]

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))

 

[T. Valko]

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.