Are you tired of using basic arithmetic operators to calculate weighted moving average in Google Sheets? Look no further! In this article, we will explore different formula options that will make your calculations a breeze.
Why Should I Use the WMA over the SMA?
Before we dive into the formulas, let’s understand the key difference between weighted moving average (WMA) and simple moving average (SMA). While SMA gives equal weights to all data points, WMA assigns different weights to each data point in a subset.
Traders often rely on WMA calculations to plot charts and make buying or selling decisions. By putting more weight on recent data points, you can get more accurate trade signals.
Calculating Weighted Moving Average in Google Sheets: Three Formula Options
Now that we understand the importance of WMA, let’s explore three different formula options available in Google Sheets.
AVERAGE.WEIGHTED Function in WMA Calculation
Start by using the AVERAGE.WEIGHTED function in Google Sheets. This function allows you to specify the weights for each data point in the subset. Simply input your data points and their corresponding weights, and the function will calculate the WMA for you.
Here’s an example formula:
=AVERAGE.WEIGHTED(B3:B5,$F$3:$F$5)In this formula, the cell range reference for the subset should be relative (without dollar signs), while the cell range reference for the weights should be absolute (with dollar signs). The resulting WMA will give you valuable insights into your trading strategy.
SUMPRODUCT Function in WMA Calculation
Another option available is to use the SUMPRODUCT function. This function allows you to multiply corresponding data points and weights, and then sum them up to calculate the WMA.
Replace the AVERAGE.WEIGHTED formula with the following SUMPRODUCT formula:
=SUMPRODUCT(B3:B5,$F$3:$F$5)Copy and paste the formula as explained in the example above to get your WMA calculation.
WMA Formula that Uses Basic Arithmetic Operators
If you prefer a more straightforward method, you can use basic arithmetic operators to calculate WMA. Multiply each data point with its corresponding weight, and then sum them up to get the final WMA.
Here’s an example formula:
=(B3*$F$3)+(B4*$F$4)+(B5*$F$5)Make sure the data points in the formula are relative, while the weights are absolute. This will ensure the formula works correctly when dragged down to calculate the WMA for different subsets.
Plotting SMA and WMA Charts In Google Sheets
Now that you have mastered the art of calculating WMAs, let’s take it a step further by plotting SMA and WMA charts in Google Sheets.
Follow these simple steps:
- Select the range A2:D14.
- Right-click on any cell in the selected range and click “Copy”.
- Click on cell A16, right-click, and select “Paste Special” > “Paste values only” to paste the values.
- Repeat steps 1 and 2 to copy the data again.
- Click cell J7, right-click, and select “Paste Special” > “Paste transposed” to paste the values.
- Delete the obsolete values in the range A16:D28.
- Select J7:V10.
- Go to the menu Insert > Chart > Line chart.
- Voila! You now have your SMA and WMA charts in Google Sheets.
Utilize these charts to visualize your trading strategies and gain valuable insights.
Explore the Possibilities with Crawlan.com
If you’re hungry for more Google Sheets tips and tricks, head over to Crawlan.com for a plethora of resources. Discover new ways to maximize your productivity and take your spreadsheet skills to the next level.
Thanks for joining us on this journey. Happy trading!
