How to Use the Weighted Average Formula in Excel

If someone asks me to find the average of five values — 1, 4, 7, 8, and 10 — the equation is easy. I add up all five values and divide this by the total number of values.

It looks like this: (1 + 4+ 7+ 8+ 10) / 5

Do the math, and 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 won’t reflect this importance since it assigns all values equal weight. While I could do the heavy lifting on paper to weight the values properly, there’s an easier way: The weighted average formula.

In this article, I’ll break down how to use this formula in Excel, offer some examples, and explore a similar formula: the weighted moving average.

Table of Contents

When to Use a Weighted Average

Use a weighted average when values have differing importance. But what exactly does that mean?

Here’s 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 look at prices on five different homes:

• $1,000,000
• $800,000
• $400,000
• $300,000
• $250,000

If I use the simple average formula, I get $550,000, which is well out of my price range. The problem? My average is wrong. This is because I haven’t accounted for how many homes are selling at each price point. Here’s the list again, but with the number of homes selling at that price in brackets.

• $1,000,000 (1)
• $800,000 (2)
• $400,000 (10)
• $300,000 (25)
• $250,000 (15)

Using the weighted average formula lets me take into account that only one home is being sold for a million dollars, while 25x more homes are at the $300,000 price point. Using the weighted average formula, I get an average of $336,792, which is right in my wheelhouse.

It’s like magic, right? 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 go over 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 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 x 15 = 1290
• 55 x 10 = 550
• 78 x 20 = 1560
• 92 x 15 = 1380
• 64 x 40 = 2560

Next, 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 on 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 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 the number of views my website received in the last five days, I can easily determine the average views in a five-day period.

Next week, I want the same value but from the most recent five days, not the five days from the previous week. This means I’m using the same amount of time but updating the data to generate an average that accounts for new data.

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

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

Here’s what it looks like:

For the first set 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 keep things simple, 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 get: 172.5

On day six, I run the weighted moving calculation again with new numbers. Our previous Day 1 is no longer applicable — it’s been replaced by the values from Day 2, which are now our Day 1. We also have a new set of data 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 just shift one day to the left. Our new Day 5, meanwhile, gets 200 views. Our weights say the same; all that changes are the number of views since they’re tied to the most recent five days.

Using our new data, our weighted moving average is calculated like so:

• 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’ll need to manually enter this formula in 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: Mastering Excel Operations

Once you get the hang of it, I think using the weighted average formula becomes pretty easy. All it takes is a little practice. While the weighted moving average is slightly more complicated, it’s 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 comprehensiveness.