Smoothing Excel Chart Data Using a Weighted Average

Excel

While smoothing using a weighted average is easy to do and understand. But, it has a problem. It gives equal weight to each time period in the average. That means your smoothed data will always lag behind the current movement in the data. A way to smooth data and still make it reactive to changing trends is to use a weighted average. Data that is more recent gets a higher weighting while data in the past is given less weight.

To smooth Excel charting data with a weighted average you need to take a percentage of each month in the range being averaged and then total the results. Of course, the percentages need to total to 100%. In this figure the formula in cell G26 is,

=G20*$E$29+F20*$E$30+E20*$E$31

Smoothing Excel chart data with a weighted average shows changes more quickly.

Smoothing Excel chart data with a weighted average shows changes more quickly.

Cells E29, E30 and E31 contain percentages. In the example E29 contains 60% so 60% of the weighting is on the first month in the average. E30 has 30% so it applies 30% of the average and the remaining 10% comes from E31. You need to make sure the total is 100%.

If you download the example and experiment you can see that by giving a higher weight to the rightmost data cell you get more advanced notice of changes in trends while still smoothing the data.

Smoothing Excel Chart Data with Fixed or Dynamic Smoothing

Related articles,

 

Share the power...