Hello and time for some more #formulafriday fun. Today we are going to look at calculating some weighted average percentages.
I looked up the meaning of weighted average and according to Investopedia –
It is an average in which each quantity to be averaged is assigned a weight, and these weightings determine the relative importance of each quantity on the average.
I myself have recently used this is in a project where the sales of one item (Item A) contributed more to a sales target for Sales people, than another item (Item B) as Item A is worth more profit than Item B so was weighted to reflect this.
Ok.. let’s get stuck into today’s formula task and use the exam grade example beginning with a sample output of student grades and the weighting given to each of the individual assessment types for their Chemistry qualifications.
A normal or non weighted average score for their Chemistry qualification would be 68. But as we know, both the Theory Exam and the Lab Practical are worth more to the overall result than both of the Assignments together, so we need to reflect that in the assessment scores.
The way this is done is to multiple each score by that scores allocated weighting then dividing the by the sum of the weightings. So if we take a look at our example the calculation would be
(67*40%+75*10%+75*10%+55*40%*)/(40+10+1+10+40) which gives a weighted average of 63.80% as opposed to a non weighted average of 86% as our student scored really high in the assignments that were weighted less that the other parts of the qualification.
So, Excel can do this waaaay quicker for us using the SUMPRODUCT Function, which is a really powerful function to have your Excel toolkit. What SUMPRODUCT does is multiplies ranges or arrays together and returns the sum of products.
Syntax Of SUMPRODUCT
- array1 – The first array or range to multiply, then add.
- array2 – [optional] The second array or range to multiply, then add.
So in our example here, we can use the SUMPRODUCT function to multiply each score by its weight and then sum those products. Then, use SUM to add up the weights and finally divide the result of SUMPRODUCT by the sum of the weights.
We can simplify this example even more as we are using percentages as weights which equal to 100%. We can omit the last part of the formula as all we are doing is dividing by 1.0. This shortens the formula to =SUMPRODUCT(E4:E7,D4:D7)
That’s it job done and easily calculated by Excel.
If you want see see all of the blog posts in the #formulafriday series then you can click on this page right here.
If you want MORE Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Formula Friday Series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
