How to Find Weighted Moving Averages in Excel?

---

A weighted moving average is a technique that can be used to smooth out time series data to reduce the “noise” in the data and more easily identify patterns and trends.

The whole idea behind a weighted moving average is to take the average of a certain number of previous periods to come up with an “average” value for a given period, while giving more weight to more recent time periods.

In this tutorial, we show how to find weighted moving averages for time series data in Excel.

Example: Weighted Moving Averages in Excel

Suppose we have the following dataset that shows the sales for a certain company during 10 periods:

We can perform the following steps to calculated weighted moving averages for this time period:

Step 1: Decide how many previous periods to include in the weighted moving average calculation.

We’ll use three periods for this example.

Step 2: Decide what weights to assign each period.

We’ll assign the weights as follows:

• 0.5 for the current period
• 0.3 for the previous period
• 0.2 for two periods back

Note that the total weights must add up to 1.

Step 3: Calculate the weighted moving average for each period.

In the image below, column C shows the weighted moving average (WMA) for time period 3 and column D shows the formula we used to calculate it:

We can use a similar formula to find the weighted moving average for every time period:

If we create a line chart to visualize the actual sales vs. the weighted moving average, we’ll notice that the WMA line is more smooth with less peaks and valleys. This is the whole idea behind a weighed moving average – it allows us to see the true underlying trend of the data without the extra noise.

Making Adjustments to the Weighted Moving Average

There are two numbers you can adjust that will lead to different weighted moving average calculations:

The number of previous periods used. In our example we used three previous periods to calculate the weighted moving averages, but we could have chosen 4, 5, 6, etc. As a rule of thumb, the more periods you use in your calculations, the smoother the weighed moving average line will be.

The weights assigned to each period. In our example we assigned the weights as 0.5, 0.3, and 0.2, but we could have chosen any combination of weights as long as they added up to 1. As a rule of thumb, the more weight you give to the most current period, the less smooth the weighted moving average line will be.

To illustrate this, consider if we again used three periods for our WMA calculation but instead used the following weights:

• 0.7 for the current period
• 0.2 for the previous period
• 0.1 for two periods back

Since we gave so much weight to the current time period, the weighted moving average line would be less smooth and more closely resemble the actual sales line:

Simple Moving Averages vs. Weighted Moving Averages

A simple moving average is a way to calculate a moving average in which all time periods used in the calculation are given the same weight.

For example, if you use three time periods to calculate the moving average then the weight given to each time period would be 0.333. Or if you use four time periods to calculate the moving average then the weight given to each period would be 0.25.

A simple moving average is more straightforward to calculate, but the benefit of using a weighted moving average is that you can assign higher weights to more recent periods. This is useful if your data is trending in a certain direction and you want to get a more accurate idea of the trend.

For example, suppose you’re calculating the weighted moving average for points scored by a basketball player who is getting better and better as the season goes on.

Using a five-game moving average, you would want to place more weight on the points scored in their most recent game so that you can get a more accurate idea of how many points they’re expected to score.

The following tutorials explain how to calculate other common metrics in Excel: