Today we will learn how to calculate weighted average in Excel with percentages.
What is weighted average ?
Weighted average or weighted mean is defined as [from wikipedia],
The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.
…If all the weights are equal, then the weighted mean is the same as the arithmetic mean.
Why should you calculate weighted average?
Well, it is because, in some situations normal averages give in-correct picture. For eg. assume you are the CEO of ACME Widgets co.. Now you are looking annual salary report and being the numbers-gal you are, you wanted to find-out the average salary of your employees. You asked each department head to give you the average salary of that department to you. Here are the numbers,
Now, the average salary seems to be $ 330,000 [total all of all salaries by 5, (55000+65000+75000+120000+1200000)/5 ].
You are a happy boss to find that your employees are making $330k per year.
Except, you are wrong. You have not considered the number of employees in each department before calculating the average. So, the correct average would be $76k as shown above.
How to Calculate Weighted Average in Excel with Percentages
There is no built-in formula in Excel to calculate weighted averages. However, there is an easy fix to that. You can use SUMPRODUCT formula. By definition, SUMPRODUCT formula takes 2 or more lists of numbers and returns the sum of product of corresponding values. [related: Excel SUMPRODUCT Formula – what is it and how to use it?]
So, if you have values in B4:B8 and the corresponding weights in C4:C8, you can use SUMPRODUCT like this to get weighted average.
=SUMPRODUCT(B4:B8, C4:C8)
Caution: However, the above method works only if C4:C8 contains weights in percentages(%) totaling to 100%.
WAvg Formula Pattern (use this with your data)
=SUMPRODUCT(<your values>, <your weights>)What if my percentage weights don’t add up to 100%?
May be your weights are more than 100 percent. Or may be they are less than 100 percent. In both cases, you can use the below formula variation.
=SUMPRODUCT(B15:B19, C15:C19) / SUM(C15:C19)
The idea is to divide the total of weights with the SUMPRODUCT result so that we can adjust Weighted Average as the weights don’t add up to 100 percent.
WAvg Formula Pattern when weights don’t add up to 100 percent
=SUMPRODUCT(<your values>, <your weights>) / SUM(<your weights>)Weighted Average when you have counts instead of weights:
If you have count of observations instead of weights, you can still use the SUMPRODUCT formula to calculate weighted average in Excel.
Here is the formula for above example:
=SUMPRODUCT(B26:B30, C26:C30) / SUM(C26:C30)
Notice that this formula is same as the formula for weighted average with weights not adding up to 100 percent.
WAvg Formula Pattern when you have counts instead of weights
=SUMPRODUCT(<your values>, <your counts>) / SUM(<your counts>)Weighted Average with Extra Conditions
Let’s say you have city wise observations and weights. And you want to calculate the weighted average, only for Boston values. In this case, you can use a variation of the formula like below:
=SUMPRODUCT((C5:C16)*(B5:B16=F5),D5:D16)/SUMIFS(D5:D16,B5:B16,F5)
How does this formula work?
- SUMPRODUCT calculates the total value for BOSTON by summing up C5:C16 (value column) where B5:B16 is Boston (highlighted portion of the formula) and multiplies that with the counts.
- So in the above example, this will just give us the total of Boston – ie 218,600
- We then divide this with the total count of Boston (using the SUMIFS formula) – ie 400
- This results in the weighted average for Boston values alone – ie 546.50
For more information on how the conditions work inside SUMPRODUCT formula, please read this article.
Download Weighted Average Calculation Example Workbook:
In this workbook, you can find 4 examples on how to to calculate weighted average in excel. Go ahead and download it to understand the formulas better.
Weighted Average in Excel – Formula Explained
Here is a video with Weighted Average formula explained. Please watch it below to learn more. Alternatively, head to my YouTube page to see the weighted averages in Excel video.
In Conclusion
Weighted averages are a great way to explain data and every data analyst should know how and when to use them with their data. Apart from Weighted Average, I suggest learning how to use moving average and average of top n values. These will help you explain the data and trends to your audience better.
Do you use Weighted Mean / Weighted Average?
What do you use it for? What kind of challenges you face? Do you apply any tweaks to weighted average calculations? Please share your ideas / tips using comments.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?