Use excel formula to provide grand total of moving averages

mejohn

New Member
Joined
Jun 23, 2018
Messages
11
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.


ColumnAValuesAverages
10.0188110.049777
20.0807420.088795
30.0968480.170795
40.2447420.290083
50.3354250.353901
60.3723760.389734
70.4070910.412808
80.4185250.425456
90.4323880.45118
100.4699730.472969
110.4759650.478446
120.4809270.496379
130.5118310.520247
140.5286620.537955
150.5472480.645084
160.742920.752024
170.7611290.782237
180.8033440.816251
190.8291590.851061
200.872963
sum of averages8.985182

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

Some videos you may like

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
Joined
Oct 31, 2015
Messages
419
Hi mejohn,
in your example the sum of averages is

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

hope this helps
 

mejohn

New Member
Joined
Jun 23, 2018
Messages
11
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top