HELP WITH EXCEL FORMULA

  • Thread starter Thread starter Stibbz
  • Start date Start date
S

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
 
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
Click to expand...


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.
 
I want my averages to be placed one below each other from cells G2 through to
G146
Click to expand...

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))
 
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.
 
Back
Top