Use the Weighted Common Components in Excel

If somebody asks me to search out the typical of 5 values — 1, 4, 7, 8, and 10 — the equation is simple. I add up all 5 values and divide this by the full variety of values.

It appears to be like like this: (1 + 4+ 7+ 8+ 10) / 5

Do the maths, and we get a median of 6. Straightforward, proper?

Now what occurs if certainly one of these values is extra essential, or “weighs” greater than the others? A easy common received’t mirror this significance because it assigns all values equal weight. Whereas I might do the heavy lifting on paper to weight the values correctly, there’s a neater means: The weighted common formulation.

On this article, I’ll break down easy methods to use this formulation in Excel, supply some examples, and discover an analogous formulation: the weighted transferring common.

Desk of Contents

When to Use a Weighted Common

Use a weighted common when values have differing significance. However what precisely does that imply?

Right here’s an instance. Let’s say I’m seeking to purchase a brand new dwelling, however I’m unsure what the typical market worth is in my neighborhood. My price range is $350,000, so I have a look at costs on 5 completely different properties:

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

If I take advantage of the straightforward common formulation, I get $550,000, which is nicely out of my value vary. The issue? My common is fallacious. It’s because I haven’t accounted for what number of properties are promoting at every value level. Right here’s the checklist once more, however with the variety of properties promoting at that value in brackets.

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

Utilizing the weighted common formulation lets me bear in mind that just one house is being offered for one million {dollars}, whereas 25x extra properties are on the $300,000 value level. Utilizing the weighted common formulation, I get a median of $336,792, which is true in my wheelhouse.

It’s like magic, proper? Right here’s the way it works.

Calculate Weighted Common in Excel

To calculate the weighted common in Excel, use the SUMPRODUCT and SUM features within the following formulation:

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

This formulation works by multiplying every worth by its weight and mixing the values. Then, you divide the SUMPRODUCT by the sum of the weights to your weighted common.

Nonetheless confused? Let’s go over the steps within the subsequent part.

Utilizing SUMPRODUCT to Calculate Weighted Common in Excel

Listed here are my steps for utilizing SUMPRODUCT.

1. I enter my information right into a spreadsheet after which add a column containing the burden for every information level.

2. Subsequent, I kind =SUMPRODUCT to begin the formulation and enter the values.

3. Lastly, I click on enter to get my outcomes.

Right here’s what’s occurring below the hood.

First, the equation multiplies every rating by its weight:

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

Subsequent, it combines these values:

1290 + 550 + 1560 + 1380 + 2560 = 7340

Lastly, the equation divides the mixed worth by the full worth of our weights:

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

Discover Weighted Shifting Averages in Excel

A helpful variation on the weighted transferring common is the weighted transferring common.

After I use a weighted transferring common, I can calculate the typical over a set interval at the same time as I add new information or give extra weight to sure values. This might help determine developments and patterns extra simply.

For instance, if I do know the variety of views my web site obtained within the final 5 days, I can simply decide the typical views in a five-day interval.

Subsequent week, I would like the identical worth however from the latest 5 days, not the 5 days from the earlier week. This implies I’m utilizing the identical period of time however updating the info to generate a median that accounts for brand spanking new information.

To discover a weighted transferring common, you give extra weight to values primarily based on time.

Within the instance above, I assign weight to web site views primarily based on recency. Newer views (people who occurred yesterday) are given extra weight than people who occurred 5 days in the past. Which means every day I calculate by transferring common, the weights change.

Right here’s what it appears to be like like:

For the primary set of 5 days, I get 100, 200, 150, 300, and 100 views. I assign every of nowadays a weight, with the latest day having the best weight. To maintain issues easy, I’m going to make use of weights that add as much as 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 common, I take advantage of the formulation from the earlier part. I multiply every worth 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 transferring calculation once more with new numbers. Our earlier Day 1 is now not relevant — it’s been changed by the values from Day 2, which are actually our Day 1. We even have a brand new set of information from Day 6 (general), which is now our Day 5.

Which means the totals for days 2, 3, 4, and 5 all apply — they simply shift in the future to the left. Our new Day 5, in the meantime, will get 200 views. Our weights say the identical; all that adjustments are the variety of views since they’re tied to the latest 5 days.

Utilizing our new information, our weighted transferring common 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)

In consequence, we get a brand new common, which is: 182.5

In Excel, you’ll have to manually enter this formulation in every relevant cell.

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

Higher Than Common: Mastering Excel Operations

When you get the dangle of it, I believe utilizing the weighted common formulation turns into fairly straightforward. All it takes is a little bit apply. Whereas the weighted transferring common is barely extra sophisticated, it’s a good way to trace efficiency information over time.

However that’s simply the tip of the Excel iceberg. With apply and a little bit assist from our Excel hacks information, you’ll be able to grasp the artwork of equations. Test it out beneath.

Editor’s observe: This submit was initially printed in April 2022 and has been up to date for comprehensiveness.