A weighted average with SUMPRODUCT
PremiumIn F1, work out the weighted average score, where each score counts as many times as its weight. Divide the SUMPRODUCT of scores and weights by the SUM of the weights.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Assignment | Score | Weight | Final grade | |||
| 2 | Quiz | 80 | 1 | ||||
| 3 | Project | 90 | 3 | ||||
| 4 | Midterm | 70 | 2 | ||||
| 5 | Final exam | 85 | 4 | ||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Premium lesson
Unlock SUMPRODUCT and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:VLOOKUPINDEX and MATCH
Need help?
Why this works
The weighted total is 80×1 + 90×3 + 70×2 + 85×4 = 830. The weights add up to 10, so the weighted average is 830 / 10 = 83. A plain AVERAGE would give 81.25, as if the quiz mattered as much as the final exam.
Common mistake: Dividing by the number of scores instead of the total weight.
The answer
- F1
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)
=SUMPRODUCT(values,weights)/SUM(weights), for example =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5).