A weighted average is an average where the predetermined weight or importance of each value in a set is taken into account.
Lots of Excel users, like teachers, associate the ‘SUMPRODUCT’ function with the weighted average.
Because why not? The ‘SUMPRODUCT’ is a perfect function to simplify the calculation of finding the weighted average.
To illustrate, here’s the formula of the weighted average:
v = value
w = weight
weighted average = [(v1 x w1)+(v2 x w2)+(v3 x w3)…]/w1+w2+w3
Notice the first half of the formula?
Yes, it’s exactly how the ‘SUMPRODUCT’ computation works.
Using the ‘SUMPRODUCT’ (and the ‘SUM’) function simplifies this formula:
weighted average =SUMPRODUCT(value,weight)]/SUM(weight)
Let’s pretend that you’re having a staff evaluation for a certain branch in your shirt business.
To come up with the weighted average, you’ll have to consider the weight of each duty.
Applying the formula above, we’ll arrive at:
weighted average =SUMPRODUCT(grade,weight)]/SUM(weight)
Here’s the result side by side with a manual computation: