Prior to discussing how to calculate weighted average in excel, let us define it. Weighted average is a calculation that takes into account the varying degrees of importance of the numbers in a data set.
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. However, in real life this is hardly the case. Some tasks are always more important than the others and that is where the weighted average comes in.
One advantage of using the weighted average method is consistency.
Formula to Calculate Weighted Average in Excel.
The Microsoft Excel doesn’t provide a special weighted average function, we use the SUMPRODUCT function and the SUM function to calculate it.
So, what the SUMPRODUCT function does is multiply the 1st value in array1 by the 1st value in array2 and so on.
To find the weighted average in excel we’ll be required to sum the product of the score and their weight then divide the result by the sum of the weights.
Example:
| Score | Weight |
| 60 | 20% |
| 82 | 70% |
| 98 | 5% |
| 92 | 5% |
We’ll begin by transferring the data to an excel spreadsheet.
Then we’ll type our formula.
After clicking OK we get our answer.
Therefore, your weighted average is 78.9.
