How to / Sums and averages
How to calculate a weighted average in Excel
Use =SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4), with the values in column B and their weights in column C.
A final grade from weighted parts
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Score | Weight | ||
| 2 | Exam | 80 | 50% | 81 | |
| 3 | Project | 90 | 30% | ||
| 4 | Homework | 70 | 20% |
In E2: =SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4) → 81
80 × 50% + 90 × 30% + 70 × 20%. The plain average of the three scores is 80.
How it works
- SUMPRODUCT multiplies each score by its weight and adds the results.
- Dividing by the total of the weights makes the answer right whether the weights add up to 100%, to 1 or to anything else.
- A score with a large weight pulls the average toward itself.
Variations
An average price, weighted by quantity
A B C D E 1 Item Price Qty 2 Pen 1.2 300 2.12 3 Notebook 3.5 100 4 Stapler 9 20 In E2:
=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4)→ 2.12Most items sold are pens, so the average price per item is close to the price of a pen. The plain average of the three prices is 4.57.
The plain average, for comparison
A B C D E 1 Task Score Weight 2 Exam 80 50% 80 3 Project 90 30% 4 Homework 70 20% In E2:
=AVERAGE(B2:B4)→ 80
Watch out for
- Ranges of different sizes. The two ranges must have the same number of rows, or SUMPRODUCT returns #VALUE!.
- Leaving out the division. SUMPRODUCT alone is only right when the weights add up to exactly 1. Dividing by their sum is always right.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- SUMPRODUCT: Multiplies matching items in arrays, then adds the results.
- SUM: Adds up numbers, cells and ranges.
- AVERAGE: The arithmetic mean of the numbers.
- AVERAGEIF: Averages the cells that meet a condition.