How to use the weighted average formula in Excel

What is the weighted average formula?

The weighted average formula is a tool used to calculate averages weighted by different values. The weighted average takes into account the different values ​​of each data point and assigns a weight, or importance, to them based on those values. This weighted average is then used to calculate the final average.

When to use a weighted average

Use a weighted average when values ​​have different importance. But what exactly does this mean?

Here is an example. Let’s say I’m looking to buy a new home, but I’m not sure what the average market value is in my neighborhood. My budget is $350,000, so I’m looking at the prices of 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 the number of homes sold at each price point. 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 allows me to take into account that a single house is sold for a million dollars, while 25 times as many houses are priced at $300,000. Using the weighted average formula, I get an average of $336,792, which is right in my wheelhouse.

It’s like magic, isn’t it? Here’s 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 you divide the SUMPRODUCT by the sum of the weights for your weighted average.

Still confused? Let’s review 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 spreadsheet, then add a column containing the weight of 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 going on under the hood.

First, the equation multiplies each score by its weight:

86 x 15 = 1290 55 x 10 = 550 78 x 20 = 1560 92 x 15 = 1380 64 x 40 = 2560

Then it combines these values:

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 variation of the weighted moving average is the weighted moving average.

When I use a weighted moving average, I can calculate the average over a defined period even if 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 the number of views my website received over the last five days, I can easily determine the average views over a five-day period.

Next week I want the same value but for the most recent 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 into account the new data.

To find a weighted moving average, you give more weight to time-based values.

In the example above, I assign weight to website views based on recency. More recent views (those from yesterday) carry more weight than those from five days ago. This means that every day that I calculate by moving average, the weights change.

This is what it looks like:

During the first series of five days, I get 100, 200, 150, 300 and 100 views. I assign each of these days a weight, with the most recent day having the highest weight. To simplify things, I’m going to use weights that add up to 100.

Day 1: 10 (x 100) Day 2: 15 (x 200) Day 3: 15 (x 150) Day 4: 25 (x 300) Day 5: 35 (x 100)

To get 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 values ​​listed, we obtain: 172.5

On the sixth day, I run the weighted moving calculation again with new numbers. Our previous day 1 is no longer applicable: it has been replaced by the values ​​from day 2, which are now our day 1. We also have a new dataset from day 6 (overall), which is now our day 5.

This means that the totals for days 2, 3, 4 and 5 all apply: they simply shift one day to the left. Our new Day 5, meanwhile, gets 200 views. Our weights say the same thing; all that changes is the number of views since they relate to the most recent 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 obtain a new average, which is: 182.5

In Excel, you will need to manually enter this formula into each applicable 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 using the weighted average formula becomes pretty simple. All it takes is a little practice. Although the weighted moving average is slightly more complicated, it is 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 tips guide, you can master the art of equations. Check it out below.

Editor’s note: This article was originally published in April 2022 and has been updated for completeness.