Weighted moving average is a data smoothing approach that gives more weight to more recent time periods. In this guide, we’re going to show you how to calculate weighted moving average in Excel.
What is weighted moving average (WMA)?
Moving average is a technique for smoothing data to reduce effects of random, short-term fluctuations. Weighted moving average is form of a moving average which assumes that current data points are more prominent since they are more relevant than earlier data points.
For example, for a 3-point WMA, the earliest data gets the lowest weight (0.2). While the second one gets a higher value, like 0.3, the recent event gets the highest, e.g., 0.5. The important point is that the sum of weight numbers should be equal to 1. Once the weights are distributed, all you need to do is multiply the data with corresponding weight and take the average of the results.
| Data | Weight | Weighted Data (Data * Weight) |
| 10 | 0.2 | 2 |
| 29 | 0.3 | 8.7 |
| 44 | 0.5 | 22 |
WMA (Sum of Weighted Data / Period): 10.9
A common way to calculate weights is to use consecutive numbers starting from 1. You need to divide each number by the sum of numbers to find each number's percentage value. You can use the following formula to find the total.
Where n = number of periods.
For example, if you calculate weighted moving average over 4 points, the sum will be 10 by calculating (4 * (4 + 1)) / 2. Thus, the weights will be 0.1, 0.2, 0.3, 0.4.
The numbers given here are just examples. You can adjust them depending on your data.
Tip:
If you are using Excel 365, you can generate these weights easily with a single formula:
The SEQUENCE function will generate an array of sequential numbers from 1 to entered value which is number_of_periods in this case.
You can simplify this formula for future updates by using the LET function as well. The function can define in-formula named ranges and allows you to deal with the cell reference only once.
Calculating weighted moving average by AVERAGE Function
Once the weights are set, using the AVERAGE function is the easiest way to calculate weighted moving average. However, you need to multiply each data set with a weight corresponding to the period to be calculated.
You can do this multiplication by using the SUMPRODUCT function. If you use Excel 365 and have the dynamic array support, simply multiply the data with weight ranges.
or
=SUMPRODUCT(B5:B7,$D$5:$D$7)/COUNT($D$5:$D$7)
Next step is to copy the formula down along with the data. The important point is to avoid using the $ signs in the range reference to keep the range relative while keeping weight range absolute. Excel adjusts relative references based on the formula cell's location. Thus, you do not need to update your formula each time.
