Weighted moving average is one form of moving average that is used to smooth out time-series data with an aim to decrease the effects of random, short-term fluctuations and to find out the patterns and trends in the data more easily. Weighted moving average considers the current data points more important as they have more relevance than the earlier data. Hence it assigns greater weight to recent data points and less weight to earlier data points. The weighted moving average in Excel is calculated by multiplying each data point in the observation with a predetermined weighting factor.

**Table of Contents**hide

**Weighted Moving Average in Excel: ****3 Suitable Methods **

Letâ€™s assume a scenario where we have temperatures of 10 consecutive days during the autumn season. We aim to smooth the data and to determine the temperature for the **11 ^{th} day**. We will use a weighted moving average to achieve our target. Weighted moving average in Excel can be calculated using three methods. In the first one, we will calculate the average while we develop the formula ourselves. We will also use

**the**

**SUMPRODUCT**

**function**in Excel to calculate the weighted moving average. We will use exponential smoothing in the third one.

Hereâ€™s an Overview of Our Dataset:

And the Visual Representation of the Dataset is this:

**1. Calculate Weighted Moving Average Using Own Formula**

**Step 1:**

- Our first step is to determine how many previous periods we are going to include in our calculation. We are going to include the previous three periods in our calculation. You can change the number of periods depending on their importance or relevance.

**Step 2:**

- We will calculate weights for our data points. An easy approach to determine weights is to use successive numbers from 1. We will divide every number by the sum of numbers to find out the percentage value that will indicate how much the number is important or relevant in calculating the weighted moving average
**(WMA)**. Use the below formula to calculate the sum of weights.

Where n = number of periods.

For example, if you calculate the weighted moving average over 3 data points, the sum will be **6** by calculating** (3 * (3 + 1)) / 2**.

Thus, the weights will be,

For two periods back, 1/6 = 0.**17**

For the period just before the current period, 2/6 = **0.33**

For the current period 3/6 = **0.5**

**Note: The total point must add up to 1**

**Step 3:**

- Now we will calculate the weighted moving average for each period. Column
**D**in the image below shows that we have calculated the weighted moving average for the temperature on**Day 3**. column**E**shows the formula we have used to calculate it.

**Step 4: **

- We can use this formula to find out the weighted moving average for the temperature of every day.

- If we create a chart to see a visual representation of the actual temperature vs. the weighted moving average, weâ€™ll notice that the linerepresenting weighted moving average
**(WMA)**is relatively smoother with fewer fluctuations.

**Read More:Â Assigning Weights to Variables in Excel**

**2. Calculate Weighted Moving Average Using the SUMPRODUCT Function in Excel**

Excel **SUMPRODUCT** function multiplies the first element of the first array with the first element of the second array. Then it multiplies the second element of the first array with the second element of the second array. And so on.

And finally, it adds all these values. See the below image to understand how this function works.

To calculate the weighted moving average using this formula, we have to do the following:

**Step 1:**

- We will write the following formula in cell
**E7**under the**WMA**.

`=SUMPRODUCT(C5:C7,$D$5:$D$7)`

- Upon pressing
**ENTER**, the weighted moving average for**Day 3**will be calculated.

**Step 2:**

- We will drag the cell
**E7**downward using the 4-ways arrow key and the weighted moving average for the following days will be calculated.

**3. Calculate Weighted Moving Average Using Exponential Smoothing**

The **Exponential Smoothing** tool in Excel calculates the moving average. However, exponential smoothing weights the values included in the moving average calculations so that more recent values have a greater effect on the average calculation and old values have a lesser effect. This weighting is accomplished through a smoothing constant.

**Step 1:**

- To calculate the weighted moving average using Exponential Smoothing, first, click the
**Data**tabâ€™s**Data Analysis**

**Note: If the Data Analysis command is not available in your version of Excel, check the Things to Remember section below.**

- When Excel displays the
**Data Analysis**dialog box, select the**Exponential Smoothing**from the list and then click**OK**.

**Step 2:**

- Now when Excel displays the
**Exponential Smoothing**dialog box, enter the data range**C5:C14**in the input range either by typing worksheet range address or by selecting the worksheet range. - Provide a smoothing constant in the
**Damping factor**input box. The Excel Help file suggests that you use a smoothing constant of between 0.2 and 0.3. We will enter**45**as the**Damping factor**or smoothing constant. - Select the range
**D5:D14**as the**Output Range**where you want to store the weighted moving average values. - Press the
**OK**button.

**Step 3:**

- Upon pressing
**ENTER**, the weighted moving average for 10 days will be calculated.

- We will create a line chart to visualize the actual temperature vs. the weighted moving average.

**Read More: Calculate Conditional Weighted Average with Multiple Conditions in Excel**

**Things to Remember**

If the **Data Analysis** command is not available in your version of Excel, you need to load the **Analysis ToolPak** add-in program. These instructions are also applicable to Excel 2010, Excel 2013, and Excel 2016.

- Â Click the
**File**tab, click**Options**, and then click the**Add-Ins**category. **Â**In the**Manage**box, select**Excel Add-ins**and then click**Go**.- In the
**Add-Ins available**box, select the**Analysis ToolPak**check box and then click**OK**.

**Tip: **If you do not find **Analysis ToolPak** in the **Add-Ins** available box, click **Browse** to locate it.

If you get a prompt box saying Analysis ToolPak is not currently installed on your computer, click **Yes** to install it.

Once you have installed the add-in successfully you will see data analysis when you click on the data tab (usually to the far right of the toolbar).

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**Conclusion**

In this article, we have learned to calculate the weighted moving average with our developed formula and using the **SUMPRODUCT** function and the **Exponential Smoothing** tool of Excel. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!