When we calculate a simple average of a given set of values, the assumption is that all the values carry an equal weight or importance. For example, if you’re appearing for exams and all the exams carry a similar weight, then the average of your total marks would also be the weighted average.
However, in real life, this is hardly the case. Some tasks are always more important than the others. Some exams are more important than the others.
And that’s where Weighted Average comes into the picture.
Here is the text book definition of Weighted Average:
Now let’s see how to calculate the Weighted Average in Excel.
Weighted Average in Excel
In this tutorial, you’ll learn:
- How to Calculate the Weighted Average in Excel using the SUMPRODUCT function.
- How to Calculate the Weighted Average in Excel using the SUM function.
So let’s get started.
Weighted Average in Excel – SUMPRODUCT Function
Suppose you have a dataset as shown below:
In the above data, a student gets marks in different evaluations, but in the end needs to be given a final score. A simple average can not be calculated here as the importance of different evaluations vary.
For example, a quiz, with a weight of 10% carries twice the weight as compared with an assignment, but one-fourth the weight as compared with the Exam.
The SUMPRODUCT function is best suited for such a scenario. Here is the formula that will give you the weighted average in Excel:
Here is how it works: Excel SUMPRODUCT function multiplies the first element of the first array with the first element of the second array. Then it multiplies the second element of the first array with the second element of the second array. And so on..
And finally, it adds all these values.
Here is an illustration to make it clear.
SUMPRODUCT is the best-suited Excel function to calculate the weighted average in Excel.
In the above case, the weights were assigned in such a way that the total added up to 100%. But in real life scenarios, it may not always be the case.
Let’s have a look at the same example with different weights.
In the above case, the weights add up to 200%.
If I use the same SUMPRODUCT formula, it would give me the wrong result.
In the above result, I have doubled all the weights, and it returns the weighted average value as 153.2. Now we know a student can’t get more than 100 out of 100, no matter how brilliant he/she is.
The reason this happens is because the weights don’t add up to 100%.
Here is the formula that will get this sorted:
In the above formula, the SUMPRODUCT result is divided by the sum of all the weights. Hence, no matter what, the weights would always add up to 100%.
Another Example to Calculate Weighted Average in Excel
Here is another common example where you can use this. Suppose you are selling three different types of products as mentioned below:
You can calculate the weighted average price per product by using the SUMPRODUCT function. Here is the formula you can use:
Dividing the SUMPRODUCT result with the SUM of quantities makes sure that the weights (in this case quantities) add up to 100%.
Weighted Average in Excel – SUM Function
To calculate the weighted average using the SUM function, you need to multiply each element, with its assigned importance in percentage.
Using the same dataset:
Here the formula that will give you the right result:
This method is alright to use when you have a couple of items. But when you have many items and weights, this method could be cumbersome and error-prone. There is shorter and better way of doing this using the SUM function.
Continuing with the same data set, here is the short formula that will give you the weighted average using the SUM function:
The trick while using this formula is to use Control + Shift + Enter, instead of just using Enter. Since SUM function can not handle arrays, you need to use Control + Shift + Enter.
When you hit Control + Shift + Enter, you would see curly brackets appear automatically at the beginning and the end of the formula (see the formula bar in the above image).
Again, make sure the weights add up to 100%. If it does not, you need to divide the result by the sum of the weights (as shown below, taking the product example):