If somebody asks me to search out the common of 5 values — 1, 4, 7, 8, and 10 — the equation is straightforward. I add up all 5 values and divide this by the whole variety of values.
It appears to be like like this: (1 + 4+ 7+ 8+ 10) / 5
Do the maths, and we get a mean of 6. Simple, proper?
![Download 10 Excel Templates for Marketers [Free Kit]](https://no-cache.hubspot.com/cta/default/53/9ff7a4fe-5293-496c-acca-566bc6e73f42.png)
Now what occurs if one among these values is extra necessary, or “weighs” greater than the others? A easy common received’t mirror this significance because it assigns all values equal weight. Whereas I may do the heavy lifting on paper to weight the values correctly, there’s a neater means: The weighted common system.
On this article, I’ll break down easy methods to use this system in Excel, provide some examples, and discover an analogous system: the weighted shifting common.
Desk of Contents
What’s the weighted common system?
The weighted common system is a instrument used to calculate averages which are weighted by completely different values. The weighted common takes into consideration the completely different values of every knowledge level and provides them a weight, or significance, primarily based on these values. This weighted common is then used to calculate the ultimate common.
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 trying to purchase a brand new house, however I’m unsure what the common market worth is in my neighborhood. My funds 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 easy common system, I get $550,000, which is nicely out of my worth vary. The issue? My common is improper. It’s because I haven’t accounted for what number of properties are promoting at every worth level. Right here’s the listing once more, however with the variety of properties promoting at that worth in brackets.
- $1,000,000 (1)
- $800,000 (2)
- $400,000 (10)
- $300,000 (25)
- $250,000 (15)
Utilizing the weighted common system lets me take note of that just one house is being offered for one million {dollars}, whereas 25x extra properties are on the $300,000 worth level. Utilizing the weighted common system, I get a mean of $336,792, which is true in my wheelhouse.
It’s like magic, proper? Right here’s the way it works.
The way to Calculate Weighted Common in Excel
To calculate the weighted common in Excel, use the SUMPRODUCT and SUM capabilities within the following system:
=SUMPRODUCT(X:X,X:X)/SUM(X:X)
This system works by multiplying every worth by its weight and mixing the values. Then, you divide the SUMPRODUCT by the sum of the weights in 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 knowledge right into a spreadsheet after which add a column containing the burden for every knowledge level.
2. Subsequent, I kind =SUMPRODUCT to begin the system and enter the values.
3. Lastly, I click on enter to get my outcomes.
Right here’s what’s taking place beneath 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 whole worth of our weights:
- 7340 / (15 + 10 + 20 + 15 + 40) = 73.4
The way to Discover Weighted Shifting Averages in Excel
A helpful variation on the weighted shifting common is the weighted shifting common.
Once I use a weighted shifting common, I can calculate the common over a set interval at the same time as I add new knowledge or give extra weight to sure values. This will help establish developments and patterns extra simply.
For instance, if I do know the variety of views my web site acquired within the final 5 days, I can simply decide the common views in a five-day interval.
Subsequent week, I need the identical worth however from the newest 5 days, not the 5 days from the earlier week. This implies I’m utilizing the identical period of time however updating the information to generate a mean that accounts for brand spanking new knowledge.
To discover a weighted shifting 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 (those who occurred yesterday) are given extra weight than those who occurred 5 days in the past. Which means every day I calculate by shifting 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 newest 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 system 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 shifting calculation once more with new numbers. Our earlier Day 1 is not relevant — it’s been changed by the values from Day 2, which at the moment are 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 newest 5 days.
Utilizing our new knowledge, our weighted shifting 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 must manually enter this system 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)] / complete weight
Higher Than Common: Mastering Excel Operations
When you get the grasp of it, I believe utilizing the weighted common system turns into fairly simple. All it takes is just a little follow. Whereas the weighted shifting common is barely extra sophisticated, it’s a good way to trace efficiency knowledge over time.
However that’s simply the tip of the Excel iceberg. With follow and just a little assist from our Excel hacks information, you possibly can grasp the artwork of equations. Test it out beneath.
Editor’s notice: This submit was initially revealed in April 2022 and has been up to date for comprehensiveness.
