=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) calculates a weighted average: each score in B is multiplied by its weight in C, the products are added up, and the total is divided by the sum of the weights.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Average | Result | |
| 2 | Homework | 85 | 20% | Weighted | 81.2 | |
| 3 | Quizzes | 78 | 30% | Plain AVERAGE | 80.75 | |
| 4 | Midterm | 72 | 20% | |||
| 5 | Final | 88 | 30% |
The weighted grade is 81.2, while a plain AVERAGE gives 80.75, because it treats the homework, worth 20%, as if it counted as much as the final, worth 30%. Change the final's score and the weighted grade moves more than it would for the same change in homework.
Excel has no WEIGHTED.AVERAGE function, so SUMPRODUCT divided by SUM is the standard formula. Google Sheets has AVERAGE.WEIGHTED(B2:B5,C2:C5).
How the weighted average formula works
SUMPRODUCT multiplies the two ranges row by row and adds the results. Written out with a helper column, it is a column of products and their SUM:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Part | Score | Weight | Score x weight |
| 2 | Homework | 85 | 20% | 17.0 |
| 3 | Quizzes | 78 | 30% | 23.4 |
| 4 | Midterm | 72 | 20% | 14.4 |
| 5 | Final | 88 | 30% | 26.4 |
| 6 | Total | 100% | 81.2 | |
| 7 | Weighted average | 81.2 |
Each part contributes its score times its weight: 85 × 20% is 17.0, 78 × 30% is 23.4, and so on. They add up to 81.2. The weights add up to 100%, so dividing by C6 changes nothing here, but it is what keeps the formula right when they do not.
Weights that do not add up to 100%
Weights do not have to be percentages. A GPA is weighted by credit hours, an average price by quantity. Dividing by SUM of the weights handles any total.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Course | Grade points | Credits | Average | Result | |
| 2 | Math | 4 | 4 | Weighted GPA | 3.51 | |
| 3 | History | 3 | 3 | Plain average | 3.48 | |
| 4 | Biology | 3.7 | 4 | Total credits | 14 | |
| 5 | Art | 2.7 | 2 | |||
| 6 | Lab | 4 | 1 |
Without the division the formula would return the sum of grade points times credits, 49.2 here, not a GPA. With it, F2 shows the GPA weighted by 14 credits. The four-credit courses pull the average toward their grades, and the one-credit lab barely moves it: change B6 to 2 and watch how little F2 changes compared with F3.
If your weights are percentages that add up to exactly 100%, =SUMPRODUCT(B2:B5,C2:C5) alone gives the same result. Keep the /SUM(...) anyway: the day a weight is changed and the total becomes 105%, the formula without it is wrong and nothing on the sheet says so.
Weighted average with a condition
To weight only some rows, multiply by a condition inside SUMPRODUCT, and add up the matching weights with SUMIF. Below, the average price per region is weighted by the quantity sold.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Price | Qty | Region | Average price | |
| 2 | North | Apple | $1.20 | 100 | North | $1.45 | |
| 3 | South | Pear | $1.50 | 40 | South | $1.36 | |
| 4 | North | Pear | $1.50 | 60 | |||
| 5 | South | Apple | $1.20 | 120 | |||
| 6 | North | Plum | $2.00 | 40 | |||
| 7 | South | Plum | $2.00 | 20 |
North sold 100 apples, 60 pears and 40 plums, so its average price is $1.45, closer to the apple price than a plain average of the three prices would be. The condition (A2:A7=F2) is 1 on North rows and 0 elsewhere, so the other rows add nothing to the top, and SUMIF adds only the North quantities at the bottom.
Practice: weighted average price
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Batch | Price | Qty | Average | Result | |
| 2 | Jan | $4.20 | 100 | Weighted price | ||
| 3 | Feb | $4.50 | 40 | |||
| 4 | Mar | $3.90 | 250 | |||
| 5 | Apr | $4.80 | 10 | |||
| 6 | May | $4.10 | 120 |
Your turn: You bought the same item in five batches at different prices. Calculate the average price per unit, weighted by the quantity of each batch. Write the formula in F2.
Mistakes that give the wrong weighted average
- AVERAGE of the products.
=AVERAGE(D2:D5)over a column of score × weight divides by the number of rows, not by the weights, and gives a small, meaningless number. Divide the SUM of the products by the SUM of the weights. - Dividing by the count instead of the weights.
=SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6)is only right when every weight is 1. - Ranges that do not line up.
=SUMPRODUCT(B2:B6,C3:C7)pairs each value with the next row's weight. Both ranges must start and end on the same rows; different sizes return#VALUE!. - A blank weight. An empty weight counts as 0, so that row is left out silently. If a missing weight should stop the calculation, check first with
=COUNTBLANK(C2:C6). - Averaging averages. Two class averages of 70 (10 students) and 90 (30 students) do not average to 80. Weight them by the class sizes and the result is 85; the AVERAGEIF page has the same trap with conditions.
Frequently Asked Questions
How do I calculate a weighted average in Excel?
Use =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5), with the values in B and the weights in C. SUMPRODUCT multiplies each value by its weight and adds the results; dividing by the sum of the weights turns that into an average.
Do the weights have to add up to 100%?
No, as long as you divide by SUM of the weights. Credits of 3, 4, 2 and 1, or weights of 2, 1 and 1, work the same way. Only the shortcut =SUMPRODUCT(B2:B5,C2:C5) without the division requires weights that add to exactly 100%.
Is there a WEIGHTED.AVERAGE function in Excel?
No. Excel has no built-in weighted average function, so the SUMPRODUCT and SUM combination is the standard formula. In Google Sheets, AVERAGE.WEIGHTED(B2:B5,C2:C5) does the same.
How do I calculate a weighted average with a condition?
Add the condition to SUMPRODUCT and use SUMIF for the weights: =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMIF(A2:A7,"North",C2:C7).
