HELP WITH EXCEL FORMULA

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
 
L

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
 
K

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.
 
L

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
 
S

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top