Use excel formula to provide grand total of moving averages

[mejohn]

Hello, I need help in finding a formula I can plug into a cell, and would give me the grand total for the moving averages.
ColumnB contains the values and the Averages column has the formula "=Average(B2:B3)", which is then copied down the column, for example =Average(B3:B4), etc.
Finally I sum up the averages column to get the sum of averages or grand total for the moving averages.

I would like a formula that uses the Values column, to provide the total for the moving averages without having to create and populate the averages column.

Also, this example has only 20 rows, the actual data contains hundreds of rows.


Thank you for your time.


[TABLE="width: 403"]
<colgroup><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD]ColumnA[/TD]
[TD][/TD]
[TD]Values[/TD]
[TD][/TD]
[TD]Averages[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]0.018811[/TD]
[TD][/TD]
[TD="align: right"]0.049777[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]0.080742[/TD]
[TD][/TD]
[TD="align: right"]0.088795[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]0.096848[/TD]
[TD][/TD]
[TD="align: right"]0.170795[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0.244742[/TD]
[TD][/TD]
[TD="align: right"]0.290083[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0.335425[/TD]
[TD][/TD]
[TD="align: right"]0.353901[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]0.372376[/TD]
[TD][/TD]
[TD="align: right"]0.389734[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]0.407091[/TD]
[TD][/TD]
[TD="align: right"]0.412808[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]0.418525[/TD]
[TD][/TD]
[TD="align: right"]0.425456[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]0.432388[/TD]
[TD][/TD]
[TD="align: right"]0.45118[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]0.469973[/TD]
[TD][/TD]
[TD="align: right"]0.472969[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]0.475965[/TD]
[TD][/TD]
[TD="align: right"]0.478446[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]0.480927[/TD]
[TD][/TD]
[TD="align: right"]0.496379[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]0.511831[/TD]
[TD][/TD]
[TD="align: right"]0.520247[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD="align: right"]0.528662[/TD]
[TD][/TD]
[TD="align: right"]0.537955[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]0.547248[/TD]
[TD][/TD]
[TD="align: right"]0.645084[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD="align: right"]0.74292[/TD]
[TD][/TD]
[TD="align: right"]0.752024[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD="align: right"]0.761129[/TD]
[TD][/TD]
[TD="align: right"]0.782237[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD="align: right"]0.803344[/TD]
[TD][/TD]
[TD="align: right"]0.816251[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD="align: right"]0.829159[/TD]
[TD][/TD]
[TD="align: right"]0.851061[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="align: right"]0.872963[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sum of averages[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8.985182[/TD]
[/TR]
</tbody>[/TABLE]

 

[B___P]

Hi mejohn,
in your example the sum of averages is

=SUM(A2:A21)-AVERAGE(A2,A21)

hope this helps

 

[kvsrinivasamurthy]

This formula works.

=SUMPRODUCT(SUMIF(OFFSET($B$2,ROW($B$2:$B$20)-ROW($B$2),0,2),"<>0")/2)

 

[mejohn]

Hi B_P, thanks for the reply.
The sum is close to the moving average but slightly less than.
I tried the formula from kvsrinivasamurthy and it gives me the correct value.

Thanks again for you help.

 

[mejohn]

Hi kvsrinivasamurthy,

I tried the formula and it works great.

Thanks again.