Ryan is an Excel master and specializes in creating simple solutions to complex problems in the workplace.
When in the course of human events, it becomes necessary to calculate a weighted average - rather than a simple average - it pays to know the SUMPRODUCT function in Excel.
Take, for instance, this dataset of total students (column A) who received certain test scores (column B). One may be tempted to just average column B and conclude an average score of 85.7, but one would also be wrong. In this case, a weighted average is needed to account for the differing numbers of students with each score.
Syntax: SUMPRODUCT(array 1, array 2, etc.)
SUMPRODUCT does exactly as it announces - It SUMS the PRODUCTS of the arrays. In long form, the average test score formula would be:
=((A2*B2)+(A3*B3)+(A4*B4)+(A5*B5)+(A6*B6)+(A7*B7)+(A8*B8)+(A9*B9)+(A10*B10)) / SUM(A2:A10)
Notice the bolded SUM of the PRODUCTS of each row. The SUMPRODUCT function allows us to condense the bolded part to just:
Explanation of Weighted Averages
A weighted average "weighs" each value differently rather than considering them all equally. In this case, the 100 has a weight of 1 because only one student obtained it, whereas nine students received a 71 so it has a weight of 9. Without accounting for the weights, the answer would be incorrect.
Numerous situations in the workplace and school require weighted averages. In such cases, it pays to know the SUMPRODUCT function.