If someone asks me to find the average of five values - 1, 4, 7, 8 and 10 – the equation is simple. I add all five values and divide by the total number of values.
It looks like this: (1 + 4+ 7+ 8+ 10) / 5
If we do the math, we get an average of 6. Easy, right?
Now what happens if one of these values is more important or “weighs” more than the others? A simple average does not reflect this meaning because it assigns equal weight to all values. While I could do the heavy lifting on paper to properly weight the values, there is an easier way: the weighted average formula.
In this article, I’ll explain how to use this formula in Excel, offer some examples, and explore a similar formula: the weighted moving average.
Table of contents
What is the weighted average formula?
The weighted average formula is a tool for calculating averages weighted by different values. Weighted average takes into account the different values of each data point and assigns them a weight or importance based on those values. This weighted average is then used to calculate the final average.
When should a weighted average be used?
Use a weighted average when values have different meanings. But what exactly does that mean?
Here is an example. Let’s say I want to buy a new house, but I’m not sure what the average market value is in my neighborhood. My budget is $350,000, so I’m looking at prices for five different houses:
- $1,000,000
- $800,000
- $400,000
- $300,000
- $250,000
If I use the simple average formula, I get $550,000, which is way out of my price range. The problem? My average is wrong. That’s because I didn’t take into account how many homes sell at each price. Here’s the list again, but with the number of homes sold at that price in parentheses.
- $1,000,000 (1)
- $800,000 (2)
- $400,000 (10)
- $300,000 (25)
- $250,000 (15)
Using the weighted average formula, I can account for only one home selling for $1 million, while 25x more homes sell for $300,000. Using the weighted average formula, I get an average of $336,792, which is right in my wheelhouse.
It’s like magic, right? This is how it works.
How to calculate weighted average in Excel
To calculate the weighted average in Excel, use the SUMPRODUCT and SUM functions in the following formula:
=SUMPRODUCT(X:X,X:X)/SUM(X:X)
This formula works by multiplying each value by its weight and combining the values. Then divide the SUM PRODUCT by the sum of the weights for your weighted average.
Still confused? Let’s go through the steps in the next section.
Using SUMPRODUCT to Calculate Weighted Average in Excel
Here are my steps for using SUMPRODUCT.
1. I enter my data into a table and then add a column containing the weight for each data point.
2. Next I type =SUMPRODUCT to start the formula and enter the values.
3. Finally, I click Enter to get my results.
Here’s what’s happening under the hood.
First, the equation multiplies each score by its weight:
- 86×15 = 1290
- 55 x 10 = 550
- 78×20 = 1560
- 92×15 = 1380
- 64×40 = 2560
Next, these values are combined:
- 1290 + 550 + 1560 + 1380 + 2560 = 7340
Finally, the equation divides the combined value by the total value of our weights:
- 7340 / (15 + 10 + 20 + 15 + 40) = 73.4
How to Find Weighted Moving Averages in Excel
A useful variant of the weighted moving average is the weighted moving average.
When I use a weighted moving average, I can calculate the average over a set period of time even as I add new data or give more weight to certain values. This can help identify trends and patterns more easily.
For example, if I know how many views my website received in the last five days, I can easily determine the average views over a five-day period.
Next week I want the same value, but from the last five days, not the five days of the previous week. This means I use the same time but update the data to generate an average that takes new data into account.
To find a weighted moving average, give values more weight based on time.
In the example above, I assign a weight to website views based on recency. More weight is given to more recent views (those that took place yesterday) than those that took place five days ago. This means that the weights change every day that I calculate the moving average.
This is what it looks like:
In the first five days I get 100, 200, 150, 300 and 100 views. I assign a weight to each of these days, with the most recent day having the highest weight. For simplicity, I use weights that add up to 100.
- Day 1: 10 (x 100)
- Day 2: 15 (x 200)
- Day 3: 15 (x150)
- Day 4: 25 (x 300)
- Day 5: 35 (x 100)
To find my weighted average, I use the formula from the previous section. I multiply each value by its weight and divide by the sum of the weights. For the listed values we get: 172.5
On the sixth day, I run the weighted motion calculation again with new numbers. Our previous Day 1 is no longer applicable – it has been replaced with the values from Day 2, which are now our Day 1. We also have a new data set from day 6 (total), which is now our day 5.
This means that the totals for days 2, 3, 4 and 5 all apply – they just shift one day to the left. Our new day 5 now has 200 views. Our weights say the same thing; Only the number of views changes, as these are tied to the last five days.
Using our new data, our weighted moving average is calculated as follows:
- Day 1: 10 (x 200)
- Day 2: 15 (x 150)
- Day 3: 15 (x 300)
- Day 4: 25 (x 100)
- Day 5: 35 (x 200)
As a result, we get a new average, which is 182.5
In Excel, you must manually enter this formula into each corresponding cell.
WMA = (Value 1 x (weight)) + (Value 2 x (weight)) + (Value 3 x (weight)) + (Value 4 x (weight)) + (Value 5 x (weight)) / Total weight
Better than average: Master Excel operations
Once you get the hang of it, I think it becomes pretty easy to use the weighted average formula. It just takes a little practice. While the weighted moving average is a little more complicated, it provides a great way to track performance data over time.
But that’s just the tip of the Excel iceberg. With practice and a little help from our Excel hacks guide, you can master the art of equations. Check it out below.
Editor’s Note: This post was originally published in April 2022 and has been updated for completeness.