How to Use the Weighted Average Formula in Excel

By mbretous@hubspot.com (Martina Bretous)

Download 10 Excel Templates for Marketers [Free Kit]

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

What is the weighted average formula?
When to Use a Weighted Average
How to Calculate Weighted Average in Excel
Using SUMPRODUCT to Calculate Weighted Average in Excel
How to Find Weighted Moving Averages in Excel

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.