Most of the Excel users use the basic arithmetic operators – addition and multiplication – to calculate WMA. What about Sheets users?

Other than the said operators, we can use SUMPRODUCT (in Excel also) or AVERAGE.WEIGHTED functions to calculate weighted moving average in Google Sheets.

What’s the purpose of calculating WMA?

You can gather a lot of information about the real purpose of WMA calculation on the web. Still, to make this tutorial justifiable to the topic, here is my explanation.

Normally, traders depend on such calculations.

I mean, they calculate weighted moving average and plot charts based on that calculation to get trade signals and thus by making buying or selling decisions.

Why Should I Use the WMA over the SMA?

Before going to learn the function to calculate the weighted moving average (WMA) in Google Sheets, we should know how it’s different from the simple moving average (SMA).

SMA is the averages of different subsets of a full data set (sample) like the average for every three months in a twelve-month dataset.

But in the subset, the average formula gives equal weights to the numbers in the subset.

Equal weights, what does that mean?

In SMA we calculate the average/mean by summing up all the data points in the subset and dividing that total by the total number of data points.

If there are 3 data points in the subset add them and then divide the sum by 3.

For example, let’s create a set of data for 6 months period.

In cell C3, enter the below AVERAGE formula and drag the fill handle down.

In WMA, we put different weighting for the data points in the subset. Do put more weighting to the most recent data point in the sub-set. All the subsets must have the same weighting.

You can understand the same from the formula options to calculate the weighted moving average (WMA) in Google Sheets below.

Calculating Weighted Moving Average in Google Sheets

As I have mentioned at the starting, you are going to get three different methods, or we can say formula options. Here are them.

Note:- Please do research online to understand more accurate and detailed information on WMA as I am not a statistician.

AVERAGE.WEIGHTED Function in WMA Calculation in Google Sheets

Here we can use the same sample data given above. But in addition to that, we may need to specify the weights.

I am using the following weights. They are 0.6, 0.3, and 0.1, which are in the cells F5, F4, and F3, respectively.

The formula in cell D5 (copy-pasted to D6:D8):

=AVERAGE.WEIGHTED(B3:B5,$F$3:$F$5)

In the formula, the subset cell-range reference must be relative (without dollar signs), and the weighting cell-range reference must be absolute (with dollar signs).

As in the result, the WMA 23.5 in cell D8 is the indicator for the 7th month.

The above is the simplest method to calculate the weighted moving average AKA WMA in Google Sheets.

SUMPRODUCT Function in Weighted Moving Average Calculation in Google Sheets

We can do the same above calculation using the SUMPRDOCUT function. I could see this type of usage among Excel users.

It would be like this. Replace the WEIGHTED.AVERAGE formula in cell D5 with the following SUMPRODUCT formula.

=SUMPRODUCT(B3:B5,$F$3:$F$5)

Copy paste the same as explained in the just above example.

WMA Formula that Uses Basic Arithmetic Operators

If you are not familiar with the above two Google Sheets functions, it may be difficult for you to understand the calculation. So here is the most popular calculation method that uses two operators.

=(B3*$F$3)+(B4*$F$4)+(B5*$F$5)

The above formula is for the cell D5. So copy-paste it down.

Here also you can note that the data points are relative and weightings are absolute.

It’s important. Otherwise, when you drag the formula, it won’t correctly pick the data for the subset down.

If there are more data points (observations) in your subset, for example 10 data points, there must be 10 weights.

Plotting SMA and WMA Charts In Google Sheets

Follow the below steps to insert simple and weighted moving average charts in Google Sheets.

For this example, we are going to use the same above data. But there are more data points added.

Steps to Follow

1. Select the range A2:D14. For that, click cell A2 > press and hold the Shift key > click on cell D14.

2. Right-click on any cell in the selected range A2:D14 and click the “Copy” command.

3. Click on the cell A16 and right-click > Paste Special > Paste values only. It will paste the values from A2:D14 to A16:D28.

4. Follow step 1 above to select the range A16:D28.

5. Follow step 2 to copy the data.

6. Click the cell J7 > right click > Paste Special > Paste transposed. The values will be pasted in J7:V10.

7. Delete the values in the range A16:D28, which are now obsolete.

8. Select J7:V10.

9. Go to the menu Insert > Chart > Line chart.

10. You will get the required SMA and WMA chart in Google Sheets.

That’s all bout the weighted moving average calculation in Google Sheets.

Thanks for the stay, enjoy!