How to Calculate a Weighted Average in Excel
A weighted average multiplies each value by a weight, sums those products, then divides by the sum of weights.
=SUMPRODUCT(A2:A10, B2:B10) / SUM(B2:B10)Guard zero total weight:
=IF(SUM(B2:B10)=0,0,SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10)) to avoid #DIV/0!.Common mistakes
- Using AVERAGE of averages (not weight-aware)
- Weights as text → #VALUE!
- Including header rows in SUMPRODUCT ranges