How to calculate a weighted average in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)Multiply each value by its weight, add them up and divide by the total weight: =SUMPRODUCT(values,weights)/SUM(weights).
- Result on the example
- 85.60 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMPRODUCT multiplies each score by its weight and adds the results. Dividing by the sum of the weights turns that into an average, and it still works when the weights don't add up to 100% or are counts like units sold.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)Result85.6
B2:B65 rows × 1 col: 92, 78, 84, 95, …A range: 5 cells in one column.
C2:C65 rows × 1 col: 20%, 15%, 25%, 15%, …A range: 5 cells in one column.
SUMPRODUCT(B2:B6,C2:C6)85.6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B65 rows × 1 col: 92, 78, 84, 95, … - array2
C2:C65 rows × 1 col: 20%, 15%, 25%, 15%, …
- array1
SUM(C2:C6)1SUM: Adds numbers and ranges. Skips text and blanks.- number1
C2:C65 rows × 1 col: 20%, 15%, 25%, 15%, …
- number1
SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)85.6The / operator divides.
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6). We ran it in Google Sheets mode on the same data and it returns 85.60.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B2:B6*C2:C6)/SUM(C2:C6) | 85.60 | The same calculation with an array multiplication; in Excel 2019 and older it needs Ctrl+Shift+Enter. |
Common mistakes
- AVERAGE(B2:B6) treats every score equally and gives 86, not the weighted 85.6.
- The values and weights ranges must be the same size, or SUMPRODUCT returns #VALUE!.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate a weighted average
F1, calculate the weighted average of the scores using the weights in C2:C6.Questions
Do the weights have to add up to 100%?
No. Dividing by SUM(weights) handles any total, such as weighting prices by units sold.
How do I calculate a weighted average price?
Use quantities as weights: =SUMPRODUCT(prices,quantities)/SUM(quantities).
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.