Weighted Average in Excel: SUMPRODUCT Made Simple
Calculate a weighted average in Excel with SUMPRODUCT and SUM: grades with credits, average price by quantity, and why plain AVERAGE misleads.
By Ayoub Farid · · 1 min read
A plain average treats every value equally. A weighted average gives some values more importance: a 4-credit course counts more than a 1-credit course, a product you sold 500 of counts more than one you sold 5 of.
The formula
=SUMPRODUCT(values, weights)/SUM(weights)Example: grades with credits
| A | B | C | |
|---|---|---|---|
| 1 | Course | Grade | Credits |
| 2 | Maths | 85 | 4 |
| 3 | Art | 95 | 1 |
| 4 | History | 70 | 3 |
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)= (85×4 + 95×1 + 70×3) / 8 = 645 / 8 = 80.6
A plain =AVERAGE(B2:B4) gives 83.3, which overstates the result because the high grade is in the smallest course.
Example: average price
Average price paid, weighted by quantity:
=SUMPRODUCT(B2:B20,C2:C20)/SUM(C2:C20)Weights as percentages
If weights add up to 100% (e.g. homework 20%, exams 50%, project 30%), you can skip the division:
=SUMPRODUCT(B2:B4,C2:C4)But dividing by SUM(C2:C4) still works and protects you if weights don't add up exactly.
Common mistakes
- Averaging averages: the average of three class averages isn't the school average unless classes are the same size.
- Ranges of different sizes →
#VALUE!. - Blank weights count as 0, which removes that row. Check for missing data.
Learn more about SUMPRODUCT and build a full grade calculator.
Frequently asked questions
How do I calculate a weighted average in Excel?
=SUMPRODUCT(values,weights)/SUM(weights).
Is there a WEIGHTED AVERAGE function in Excel?
No, SUMPRODUCT divided by SUM is the standard method.
What is the difference between average and weighted average?
A simple average weights every value equally; a weighted average gives each value a weight.
Does this work in Google Sheets?
Yes. Google Sheets also has AVERAGE.WEIGHTED(values, weights).