What is the formula for weighted moving average in Excel?

How to Calculate the Weighted Moving Average

When calculating the weighted moving average, the recent data points are assigned a greater weighting, whereas past data points are assigned less weighting. It is used when the figures in the data set come with different weights, relative to each other. The sum of the weight should be equal to 1 or 100%.

It is different from the simple moving average, where all numbers are assigned an equal weighting. The final weighted moving average value reflects the importance of each data point, and it is, therefore, more descriptive of the frequency of concurrency than the simple moving average.

Example 1

Follow the following steps when calculating weighted moving average:

1. Identify the numbers you want to average

The first step is to create a list of the numbers for which the user needs to find the weighted average. Here, we can use the closing prices of ABC stock for the period starting January 1 to January 5. The closing prices are $90, $88, $89, $90, and $91, with the first number being the most recent.

2. Determine the weights of each number

After identifying the numbers for which to calculate the weighted average, the next step is to determine the weight of each number. The equation we use for weighting each number is the day number divided by the sum of all the day numbers.  Since we are looking at five days, the sum of all the day numbers in this example is 15 (i.e., 5 + 4 + 3 + 2 + 1). We give the greatest weighting to the most recent data point, as shown in the table below:

3. Multiply each number by the weighting factor

After determining the weighting for each number, the next step is to multiply each of the numbers from January 1 to 5 by the corresponding weighting factor and then sum up the resulting values. It is shown below:

The formula for the weighted moving average is expressed as follows:

Where:

4. Add up resulting values to get the weighted average

The final step is to add up the resulting values to get the weighted average for the closing prices of ABC Stock.

WMA = $30.00 + $23.47 + $17.80 + $12.00 + $6.07

WMA = $89.34

Therefore, the weighted moving average for the period from January 1 to January 5 is $89.34.

Example 2

Assume that the number of periods is 4, and we want a weighted moving average of four stock prices of $70, $66, $68, and $69, with the first price being the most recent.

Using the information given, the most recent weighting will be 4/10, the previous period before that will be 3/10, and the next period before that will be 2/10, and the initial period weighting will be 1/10. Recall that we get the denominator of 10 by adding the periods together.  In this instance, 4 + 3 + 2 + 1 = 10.

The weighting average for the four different prices will be calculated as follows:

WMA = [70 x (4/10)] + [66 x (3/10)] + [68 x (2/10)] + [69 x (1/10)]

WMA = $28.00 + $19.80 + $13.60 + $6.90 = $68.30

Simple Moving Average vs. Weighted Moving Average

Simple moving average and weighted moving average are the two widely used statistics in the world, and they are used to find the average of observations in a data set.

The main difference between the two statistical measures is that simple moving average calculates the average by summing up all the observations in a data set and dividing the total by the total number of observations. In simple terms, it applies equal weighting to all the observations in the sample.

On the other hand, weighted moving average assigns a specific weight or frequency to each observation, with the most recent observation being assigned a greater weight than those in the distant past to obtain the average.

Related Readings

Thank you for reading CFI’s guide to Weighted Moving Average. To keep learning and developing your knowledge base, please explore the additional relevant resources below:

In Example 1 of Simple Moving Average Forecast, the weights given to the previous three values were all equal. We now consider the case where these weights can be different. This type of forecasting is called weighted moving average. Here we assign m weights w1, …, wm, where w1 + …. + wm = 1, and define the forecasted values as follows

In the simple moving average method all the weights are equal to 1/m.

Example 1: Redo Example 1 of Simple Moving Average Forecast where we assume that more recent observations are weighted more than older observations, using the weights w1 = .6, w2 = .3 and w3 = .1 (as shown in range G4:G6 of Figure 1).

Figure 1 – Weighted Moving Averages

The formulas in Figure 1 are the same as those in Figure 1 of Simple Moving Average Forecast, except for the forecasted y values in column C. E.g. the formula in cell C7 is now =SUMPRODUCT(B4:B6,G$4:G$6).

The forecast for the next value in the time series is now 81.3 (cell C19), by using the formula =SUMPRODUCT(B16:B18,G$4:G$6).

Real Statistics Data Analysis Tool: Excel doesn’t provide a weighted moving averages data analysis tool. Instead, you can use the Real Statistics Weighted Moving Averages data analysis tool.

To use this tool for Example 1, press Ctr-m, choose the Time Series option from the main menu and then the Basic forecasting methods option from the dialog box that appears. Fill in the dialog box that appears as shown in Figure 5 of Simple Moving Average Forecast, but this time choose the Weighted Moving Averages option and fill in the Weights Range with G4:G6 (note that no column headings are included in the weights range). None of Parameter values are used (essentially # of Lags will be the number of rows in the weights range and # of Seasons and # of Forecasts will default to 1).

The output will look just like the output in Figure 2 of Simple Moving Average Forecast, except that the weights will be used in calculating the forecast values.

Example 2: Use Solver to calculate the weights which produce the lowest mean squared error MSE.

Using the formulas in Figure 1, select Data > Analysis|Solver and fill in the dialog box as shown in Figure 2.

Figure 2 – Solver dialog box

Note that we need to constrain the sum of the weights to be 1, which we do by clicking on the Add button. This brings up the Add Constraint dialog box, which we fill in as shown in Figure 3 and then click on the OK button.

Figure 3 – Add Constraint dialog box

We next click on the Solve button (on Figure 2), which modifies the data in Figure 1 as shown in Figure 4.

Figure 4 – Solver Optimization

As can be seen from Figure 4, Solver changes the weights to 0, .223757 and .776243 in order to minimize the value of MSE. As you can see, the minimized value of 184.688 (cell E22 of Figure 4) is at least less than the MSE value of 191.366 in cell E22 of Figure 2). To lock in these weights you need to click on the OK button of the Solver Results dialog box shown in Figure 4.