"Moving Average" | Averaging every 12 cells

[cubs610]

Hello all....

I have a spreadsheet with apprx. 50,000 vertical cells and need a formula that would average every 12 cells. How would I create one? Is this possible easily, or would a macro work better? If so, how would the code look?

Thanks,
Dave

 

[SirJB7]

Hi, cubs610!
This question has been posted widely at this forums, tried yet using the built-in search feature using the proper keywords (moving average, rolling average)?
But take care when finding solutions with OFFSET function, since it's a volatile function and you'll be having 50K of them.
If nothing found, which I doubt, or if tested and not performant then come back and someone surely will be able to help you thru the VBA code.
Regards!

 

[Sajan]

Hello Dave,
Not sure if you are looking to calculate the average of every 12th value in your range, or calculate the average for cells 1-12, then 13-24, etc.

If the former, you could try something like:
=AVERAGE(IF(MOD(ROW(A1:A15), 12)=0, A1:A15))

If the latter, you could try something like:
=IF(MOD(ROW(),12)=0,AVERAGE(A1:A12),0)

copy down to all of the rows.

Cheers,
Sajan.

 

[SirJB7]

Hi, cubs610!

Another formula approach:
A1:C1 : titles (Value, Avg each 12, Avg last 12)
A column: your data
B13: =SI(RESIDUO(FILA()-1;12)=0;PROMEDIO(A2:A13);"") -----> in english: =IF(MOD(ROW()-1,12)=0,AVERAGE(A2:A13),"")
C13: =SI(FILA()-1>=12;PROMEDIO(A2:A13);"") -----> in english: =IF(ROW()-1>=12,AVERAGE(A2:A13),"")

Copy B13:C13 down and up as required (thru 50001 and up to 2, for 50K rows). Note that range B2:C12 won't have value or will present unresolved formula references due to the border condition of the 12 groups.

Regards!

 

[r1c1]

If you just need average of every 12 cells (ie one average for cells 1-12, next average for 13-24...), then you can also use Pivot tables.

1. First in your data, add an extra column.
2. Fill it with twelve 1s, twelve 2s, twelve 3s... You can do this with below formula:

   1. =INT(ROWS($A$1:A1)/12)+1

      Assumes your data starts at A1.
3. Now select your data + new column and create a pivot.
4. Drop new column in to row labels area
5. Drop values in to values area
6. Change summary type to Average
7. You are done!

 

[cubs610]

Hey Everyone... once again thanks. I'll give these solutions a go and see what happens.

 

[ambily chacko]

Hello Dave,
Not sure if you are looking to calculate the average of every 12th value in your range, or calculate the average for cells 1-12, then 13-24, etc.

If the former, you could try something like:
=AVERAGE(IF(MOD(ROW(A1:A15), 12)=0, A1:A15))

If the latter, you could try something like:
=IF(MOD(ROW(),12)=0,AVERAGE(A1:A12),0)

copy down to all of the rows.

Cheers,
Sajan.

hello
i have a panel data of sales for 10 years how do i find average of every 12th row . i tried the above formula but only the first 12 rows average is coming.. wht to do