How to calculate a weighted average in Excel
A plain average treats every item the same. A weighted average lets some items count more, such as a 4-credit course counting four times as much as a 1-credit one.
Multiply each value by its weight, add those up, then divide by the total weight. SUMPRODUCT does the first two steps at once.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Course | Grade | Credits |
| 2 | Accounting | 82 | 4 |
| 3 | Law | 70 | 2 |
| 4 | Statistics | 91 | 3 |
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)Result: 82.33
The weighted average is about 82.3. A plain average of the grades would be 81, because it ignores the credits.
Step by step
- Type =SUMPRODUCT( and select the values.
- Add a comma and select the weights, then close the bracket.
- Type /SUM( and select the weights again.
- Close the bracket and press Enter.
Other ways to write it
=(B2*C2+B3*C3+B4*C4)/SUM(C2:C4)The same thing written out longhand. Fine for three rows, painful for three hundred.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Using AVERAGE
- AVERAGE gives every row equal weight, which is exactly what a weighted average avoids.
- Forgetting to divide
- SUMPRODUCT alone gives the total of value × weight, not an average.
Reading isn’t the same as doing
Multiply and add with SUMPRODUCT gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.