Guide · Spreadsheets
Weighted Average in Excel
Updated: June 2026
Excel has no single WEIGHTEDAVERAGE function, which sends a lot of people hunting. The trick is to combine two functions you already know — SUMPRODUCT and SUM — into one clean formula that works in Excel and Google Sheets alike. Here's the formula, the steps, and the errors that catch people out.
Free · No upload · Instant in the browser
The one formula to remember
Put your values in one column and your weights in another, then use:
SUMPRODUCT multiplies each value by the weight in the matching row and adds all those products — that's the Σ(value × weight) part. Dividing by SUM(weights) finishes the weighted average. If your values sit in B2:B5 and weights in C2:C5, the live formula is =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5).
Step by step
- Column B — type your values (grades, prices, scores) in B2 downward.
- Column C — type the matching weights (coefficients, credits, quantities) alongside.
- Pick a result cell and enter
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). - Press Enter. The weighted average appears, and it updates whenever you change a value or weight.
The two ranges must be the same height. SUMPRODUCT pairs them row by row, so B2:B5 with C2:C6 will not line up.
A worked layout
| Cell | B (value) | C (weight) |
|---|---|---|
| Row 2 | 14 | 2 |
| Row 3 | 11 | 3 |
| Row 4 | 17 | 1 |
SUMPRODUCT gives 14×2 + 11×3 + 17×1 = 78, SUM(C2:C4) gives 6, and the formula returns 78 ÷ 6 = 13. Exactly what the calculator on this site shows for the same numbers — handy for sanity-checking a sheet before you trust it.
If your weights are percentages totalling 100
When the weights in column C already add to 100% (or to 1), you can divide by that constant instead of SUM: =SUMPRODUCT(B2:B5,C2:C5)/100. But keeping /SUM(C2:C5) is safer — it stays correct even if you add a row or your weights don't quite total 100, which is the more common real-world situation.
Common errors and fixes
- #VALUE! — usually text or a blank where a number should be. SUMPRODUCT chokes on non-numbers; clean the ranges.
- Mismatched ranges — the value and weight ranges must be identical in size and orientation.
- #DIV/0! — SUM(weights) is zero. Check you actually entered the weights.
- Wrong answer, no error — you likely swapped values and weights, or included a header row in the range.
When you just need a quick figure without building a sheet, the in-browser calculator does the same job — type the pairs and read the weighted average, the simple average and each row's weight share instantly, with nothing uploaded.
Frequently asked questions
What is the Excel formula for a weighted average?
=SUMPRODUCT(values, weights)/SUM(weights). SUMPRODUCT multiplies each value by its weight and sums the results; dividing by SUM of the weights gives the weighted average.
Does the same formula work in Google Sheets?
Yes. Google Sheets supports SUMPRODUCT and SUM with the same syntax, so =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) behaves identically.
Why not use AVERAGE?
AVERAGE ignores weights and treats every value equally, so it gives a simple average. You need SUMPRODUCT to bring the weights in.
Why do I get #VALUE!?
One of the cells in your ranges holds text or is blank. SUMPRODUCT needs numbers throughout, so clear or fix any non-numeric cells.