How to Use the Weighted Average Formula in Excel

If personification asks maine to find nan mean of 5 values — 1, 4, 7, 8, and 10 — nan equation is easy. I adhd up each 5 values and disagreement this by nan full number of values.

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

Do nan math, and we get an mean of 6. Easy, right?

Now what happens if 1 of these values is much important, aliases “weighs” much than nan others? A elemental mean won’t bespeak this value since it assigns each values adjacent weight. While I could do nan dense lifting connected insubstantial to weight nan values properly, there’s an easier way: The weighted mean formula.

In this article, I’ll break down really to usage this look successful Excel, connection immoderate examples, and research a akin formula: nan weighted moving average.

Table of Contents

• What is nan weighted mean formula?
• When to Use a Weighted Average
• How to Calculate Weighted Average successful Excel
• Using SUMPRODUCT to Calculate Weighted Average successful Excel
• How to Find Weighted Moving Averages successful Excel

When to Use a Weighted Average

Use a weighted mean erstwhile values person differing importance. But what precisely does that mean?

Here’s an example. Let’s opportunity I’m looking to bargain a caller home, but I’m not judge what nan mean marketplace worth is successful my neighborhood. My fund is $350,000, truthful I look astatine prices connected 5 different homes:

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

If I usage nan elemental mean formula, I get $550,000, which is good retired of my value range. The problem? My mean is wrong. This is because I haven’t accounted for really galore homes are trading astatine each value point. Here’s nan database again, but pinch nan number of homes trading astatine that value successful brackets.

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

Using nan weighted mean look lets maine return into relationship that only 1 location is being sold for a cardinal dollars, while 25x much homes are astatine nan $300,000 value point. Using nan weighted mean formula, I get an mean of $336,792, which is correct successful my wheelhouse.

It’s for illustration magic, right? Here’s really it works.

How to Calculate Weighted Average successful Excel

To cipher nan weighted mean successful Excel, usage nan SUMPRODUCT and SUM functions successful nan pursuing formula:

=SUMPRODUCT(X:X,X:X)/SUM(X:X)

This look useful by multiplying each worth by its weight and combining nan values. Then, you disagreement nan SUMPRODUCT by nan sum of nan weights for your weighted average.

Still confused? Let's spell complete nan steps successful nan adjacent section.

Using SUMPRODUCT to Calculate Weighted Average successful Excel

Here are my steps for utilizing SUMPRODUCT.

1. I participate my information into a spreadsheet and past adhd a file containing nan weight for each information point.

2. Next, I type =SUMPRODUCT to commencement nan look and participate nan values.

3. Finally, I click participate to get my results.

Here’s what’s happening nether nan hood.

First, nan equation multiplies each people 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, nan equation divides nan mixed worth by nan full worth of our weights:

• 7340 / (15 + 10 + 20 + 15 + 40) = 73.4

How to Find Weighted Moving Averages successful Excel

A useful variety connected nan weighted moving mean is nan weighted moving average.

When I usage a weighted moving average, I tin cipher nan mean complete a group play moreover arsenic I adhd caller information aliases springiness much weight to definite values. This tin thief place trends and patterns much easily.

For example, if I cognize nan number of views my website received successful nan past 5 days, I tin easy find nan mean views successful a five-day period.

Next week, I want nan aforesaid worth but from nan astir caller 5 days, not nan 5 days from nan erstwhile week. This intends I’m utilizing nan aforesaid magnitude of clip but updating nan information to make an mean that accounts for caller data.

To find a weighted moving average, you springiness much weight to values based connected time.

In nan illustration above, I delegate weight to website views based connected recency. More caller views (those that happened yesterday) are fixed much weight than those that happened 5 days ago. This intends that each time I cipher by moving average, nan weights change.

Here’s what it looks like:

For nan first group of 5 days, I get 100, 200, 150, 300, and 100 views. I delegate each of these days a weight, pinch nan astir caller time having nan highest weight. To support things simple, I’m going to usage weights that adhd 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 usage nan look from nan erstwhile section. I multiply each worth by its weight and disagreement by nan sum of nan weights. For nan values listed, we get: 172.5

On time six, I tally nan weighted moving calculation again pinch caller numbers. Our erstwhile Day 1 is nary longer applicable — it’s been replaced by nan values from Day 2, which are now our Day 1. We besides person a caller group of information from Day 6 (overall), which is now our Day 5.

This intends that nan totals for days 2, 3, 4, and 5 each use — they conscionable displacement 1 time to nan left. Our caller Day 5, meanwhile, gets 200 views. Our weights opportunity nan same; each that changes are nan number of views since they’re tied to nan astir caller 5 days.

Using our caller data, our weighted moving mean is calculated for illustration 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 caller average, which is: 182.5

In Excel, you’ll request to manually participate this look successful each applicable cell.

WMA = [value 1 x (weight)] + [value 2 x (weight)] + [value 3 x (weight)] + [value 4 x (weight)] + [value 5 x (weight)] / full weight

Better Than Average: Mastering Excel Operations

Once you get nan bent of it, I deliberation utilizing nan weighted mean look becomes beautiful easy. All it takes is simply a small practice. While nan weighted moving mean is somewhat much complicated, it’s a awesome measurement to way capacity information complete time.

But that’s conscionable nan extremity of nan Excel iceberg. With believe and a small thief from our Excel hacks guide, you tin maestro nan creation of equations. Check it retired below.

Editor's note: This station was primitively published successful April 2022 and has been updated for comprehensiveness.