Weighted Average Formula in Excel
Weighted Average Formula is a Excel function that sumproduct multiplies each score by its weight, sums the products, then divides by the total weight. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Regular AVERAGE treats all values equally. Weighted average accounts for importance — grades, portfolio weights, survey responses.
The Formula
"Calculate weighted average of exam scores with different credit weights"
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)SUMPRODUCT multiplies each score by its weight, sums the products, then divides by the total weight. This gives a true weighted average.
Step-by-Step Breakdown
- B2:B10 — the values (exam scores)
- C2:C10 — the weights (credit hours or importance)
- SUMPRODUCT(B2:B10,C2:C10) — sum of (score * weight) for each pair
- SUM(C2:C10) — total weight (denominator)
- Division gives the weighted average
Edge Cases & Warnings
- Weights summing to 0 causes #DIV/0! — add IFERROR
- Weights don't need to sum to 1 or 100 — the formula normalizes automatically
- Empty cells are treated as 0 in both ranges
- Mismatched range sizes cause #VALUE! error
Examples
"Scores: [90, 80, 70], Weights: [3, 2, 1]"
(270+160+70)/6 = 83.33"Equal weights [1, 1, 1]"
Same as regular AVERAGE: 80Frequently Asked Questions
Why not just use AVERAGE?
AVERAGE gives equal importance to all values. A 3-credit course should matter more than a 1-credit course in your GPA. Weighted average handles this correctly.
Can I add conditions to a weighted average?
Yes. Use the conditional SUMPRODUCT pattern: =SUMPRODUCT((A2:A10="Math")*B2:B10*C2:C10)/SUMPRODUCT((A2:A10="Math")*C2:C10) for weighted average of only Math scores.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.