Excel fill with averages

[davidbr55]

I am trying to convert monthly data into quarterly data. So, I am
trying to average the numbers from each month composing the quarters,
and then pick-up from the following month. I guess this is better
explained in a formula. The formulas for two consecutive rows should
be:

=AVERAGE(Monthly!C2:C5)
=AVERAGE(Monthly!C6:C9)

So, the first number of the second row should be the next one after
the first row started and they will always add 4 numbers (rows).

But, if I try to get excel to fill in the data, I get (which is not
what I want):

=AVERAGE(Monthly!C2:C5)
=AVERAGE(Monthly!C3:C6)

This is quite a long spreadsheet, so it would be really great if I
could automate the process. I am thinking I need to use OFFSET, but I
don't know how to exactly. Could you please help me?

Thank you very much,
David Santos

P.S.: By the way, my thesis is due in about 20 hours, so this a very
time-sensitive matter

 

[Guest]

Try something like this:

A2:
=AVERAGE(INDEX(Monthly!$C:$C,(ROW()-2)*4+2):INDEX(Monthly!$C:$C,(ROW()-2)*4+3))

Copy that formula down as far as you need

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Peo Sjoblom]

Using OFFSET

=AVERAGE(OFFSET($C$2,ROWS($A$1:A1)*4-4,,4,))

however it is a volatile formula which will slow down the workbook

=AVERAGE(INDEX(C:C,ROWS($A$1:A1)*4-2):INDEX(C:C,ROWS($A$1:A1)*4+1))

should work as well and is non volatile

 

[Guest]

generate a pivot table. Put the date in the row field and the data in the
data field. Select an item in the row field (shoul be a date) select group in
the new windows select group by: quarter.
Select a data items right click - select Field setting. in the open windows
under summarize by: select Average.
this should do the job.
Andrea

 

[davidbr55]

Hi everyone,

Thanks for all your comments. I ended up using the Pivot Table (I
should have thought about that to begin with), but I did learn how to
use OFFSET and INDEX as well.

Best,
David