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.

ColumnA		Values		Averages
1		0.018811		0.049777
2		0.080742		0.088795
3		0.096848		0.170795
4		0.244742		0.290083
5		0.335425		0.353901
6		0.372376		0.389734
7		0.407091		0.412808
8		0.418525		0.425456
9		0.432388		0.45118
10		0.469973		0.472969
11		0.475965		0.478446
12		0.480927		0.496379
13		0.511831		0.520247
14		0.528662		0.537955
15		0.547248		0.645084
16		0.74292		0.752024
17		0.761129		0.782237
18		0.803344		0.816251
19		0.829159		0.851061
20		0.872963
sum of averages				8.985182

<colgroup><col span="2"><col span="3"></colgroup><tbody>
</tbody>

 

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