Course outline / Mathematical functions
Lesson 48 of 75 · Intermediate · 3 exercises
Weighted averages
Give some values more importance than others with SUMPRODUCT.
What you'll practise
- Start with a simple average
- Weight the scores with SUMPRODUCT
- When weights are not percentages
Start with a simple average
A simple average treats every score as equally important.
In cell E2, find the simple average of the scores in B2:B4.
Weight the scores with SUMPRODUCT
In a weighted average, each score counts according to its weight. Here the exam is worth 50% of the grade.
When the weights add up to 100%, multiply each score by its weight and add the results. SUMPRODUCT does exactly that in one step.
In cell E2, use SUMPRODUCT on the scores in B2:B4 and the weights in C2:C4.
When weights are not percentages
Weights are often given as points, such as 5, 3 and 2, which don't add up to 100%. Then divide by the total weight:
=SUMPRODUCT(scores, weights) / SUM(weights)
In cell E2, work out the weighted average of B2:B4 using the weights in C2:C4.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.