# Use excel formula to provide grand total of moving averages

#### mejohn

##### New Member
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.

 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>

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### B___P

##### Active Member
Hi mejohn,
in your example the sum of averages is

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

hope this helps

#### kvsrinivasamurthy

##### Well-known Member
This formula works.

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

#### mejohn

##### New Member
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.

Replies
4
Views
46
Replies
3
Views
97
Replies
1
Views
34
Replies
0
Views
229
Replies
2
Views
288