Skip to main content

Weighted average with SUMPRODUCT

50%
SUMPRODUCTSUM

AVERAGE treats every value as equally important. A module worth five credits and a module worth two are not equally important, so the plain average of the three scores here is simply the wrong number.

A weighted average multiplies each value by its weight, adds those up, and divides by the total weight. SUMPRODUCT does the multiplying and adding in one step: =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4).

Dividing by the total weight rather than by a hardcoded number is what makes it survive a fourth module being added.

In B9, work out the weighted average mark across all the modules.

The grid for this exercise is part of the paid half.

Everything above is yours to read either way. 46 exercises stay free with a profile.