Weighted Moving (Rolling) Average – Excel and Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Dennis Madrid

Last updated on June 7, 2022

This tutorial will demonstrate how to calculated the weighted moving average in Excel and Google Sheets.

Weighted Moving Rolling Average in Excel

Moving (Rolling or Running) Average is a series of averages that shows the averages of different fixed subsets of the entire dataset. For example, a 5-days moving average shows the average of the previous 5-days.Moving Average is used in time series data analysis to smoothen out short-term fluctuations in a dataset and thus be able to highlight and predict longer-term trends or cycles. The moving average can be calculated as a simple, weighted, or exponential moving average.

The Weighted Moving Average calculates the moving average of a subset of  data points where each data point has some weights assigned to them. In this article, we will look at how to calculate the weighted moving average.


Weighted Moving Average Formula

The -data points weighted moving average (k) is calculated using the formula:

Weighted Moving Average Formula

where xn-k+1 + xn-k+2 + … + xn are the current data value and the previous k-1  data values;

WMAk is the k-data points weighted moving average of  k data points up to the current data point;

w1, w2,…,wk are the weights of k data points up to the current data points expressed as percentages of the total weight; and

k is the desired fixed subset of the dataset.

How to Calculate Weighted Moving Average in Excel

Background: The closing prices of Apple Inc. stock on NASDAQ for the sixteen trading days from 2nd to 23rd May 2022 are shown in the table below. Calculate the 5-days moving averages for the stock prices using the weights 0.3, 0.25, 0.2, 0.15, and 0.1 for the current day, the previous day, the next previous day, etc., respectively.

Data Closing Prices of Apple Inc Stock


First, insert the weights in a different table as shown below.

Weights Table

Next, calculate the 5-days moving average. We will start from the 5th data, i.e. May-06. Multiply  to the 4th farthest data point,  to the 3rd farthest data point,  to the 2nd farthest data point,  to the previous data point, and  to the current data point. Repeat this process for all the subsequent weighted moving averages. The calculation is shown below.

5 Days Weighted Moving Average in Excel

Autofill the rest of the column and you have the complete 5-days Weighted Moving Average column as shown below.

5 Days Weighted Moving Average Column in Excel


Weighted Moving Average in Google Sheets

Weighted Moving Average can be calculated in Google Sheets in a similar way as it is calculated in Excel as shown in the picture below.

5 Days Weighted Moving Average in Google Sheets

AI Formula Generator

Try for Free