How to calculate a weighted average in Excel
To calculate a weighted average in Excel, multiply each value by its weight, add the products and divide by the sum of the weights: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Plain AVERAGE treats every value as equally important, so it is wrong when some values count more than others. This guide shows the formula, a check, and the usual mistakes.
1. When you need a weighted average
Use a weighted average when the items do not count equally. A course where the exam is 50% of the grade, a price average where one product sold far more units, or a score where one criterion matters most. Put the values in one column and their weights in the next, with one row per item.
2. The formula
SUMPRODUCT multiplies the cells of two ranges row by row and adds the products, so you do not need a helper column. Divide by SUM of the weights to get the average. The weights can be percentages, points or counts, because the division scales them.
Example: A course grade with three parts
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Weighted average | |
| 2 | Exam | 80 | 50 | 81 | |
| 3 | Project | 90 | 30 | ||
| 4 | Quiz | 70 | 20 |
80 times 50 plus 90 times 30 plus 70 times 20 is 8,100. Divided by the weights total of 100, the result is 81.
3. Compare it with a plain average
The same three scores give a plain AVERAGE of 80, because the formula treats the quiz as important as the exam. The weighted result of 81 is higher, because the strong project score counts for more than the weak quiz. If the two results are identical, check that the weights are not all equal.
Example: The plain average of the same scores
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Part | Score | Plain average | ||
| 2 | Exam | 80 | 80 | ||
| 3 | Project | 90 | |||
| 4 | Quiz | 70 |
240 divided by 3 is 80, which ignores the weights.
4. Weights as percentages
If your weights are percentages that already add up to 100%, you can skip the division: =SUMPRODUCT(B2:B4,C2:C4). Keep the division anyway when you are not sure, because it still gives the right answer when the weights add up to something else, and it stays correct when you add a row.
5. Weights that are quantities
For an average price, the weights are the units sold. Put the prices in one column and the units in the next, and use the same formula. It gives the real average price per unit, which is not the same as the plain average of the prices.
Common mistakes
Ranges of different sizes. SUMPRODUCT of B2:B4 and C2:C5 returns #VALUE!.
Fix: Make both ranges the same number of rows.
Leaving out the division. =SUMPRODUCT(B2:B4,C2:C4) is a weighted sum, not an average, unless the weights add up to 1.
Fix: Divide by SUM of the weights.
Text in a weight cell. SUMPRODUCT treats text as zero, so the item silently disappears from the result.
Fix: Check that every weight is a number, and use COUNT to verify how many there are.
Frequently asked questions
Is there a built-in weighted average function in Excel?
No. SUMPRODUCT divided by SUM is the standard answer.
Do the weights have to add up to 100?
No. When you divide by the sum of the weights, any scale works: 5, 3 and 2 give the same result as 50, 30 and 20.
Can I ignore blank cells?
A blank score counts as 0 in SUMPRODUCT. To leave it out, filter the rows first or use a helper column that is empty when the score is blank.
Related functions
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.
- SUMSUM adds up numbers, cells and whole ranges in one formula.
- AVERAGEIFAVERAGEIF returns the mean of the values that meet one condition, such as the average order in one region.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
More on weighted averages
The guide gives the SUMPRODUCT formula and a check. This piece adds how to build the table that feeds it, how to use a weighted average with conditions, and a way to test the result by hand.
Build the table so the formula stays simple
Keep values and weights in two columns side by side, one row per item, with a header on top. Name the weights as percentages if they come that way. Leave no blank row inside, because both ranges must have the same shape. When you add an item later, insert the row inside the range so the formula grows with it. Inserting below the last row does not extend the range, so check the formula after every change. An Excel table solves this, because its ranges grow on their own when a row is added at the bottom.
A weighted average for one group
To average only the rows of one category, multiply by a condition inside SUMPRODUCT. A comparison returns TRUE or FALSE, and multiplying turns them into 1 and 0, so the other rows count as zero.
=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMPRODUCT((A2:A7="North")*C2:C7)Check the result by hand
A weighted average must fall between the smallest and the largest value. If it does not, the weights are wrong. Test with two rows: values 10 and 20 with weights 1 and 3 give (10 + 60) / 4 = 17.5, closer to 20 because that value counts three times. Compare the result with the plain AVERAGE: the gap shows how much the weights matter.
Practice in [the SUMPRODUCT lesson](/learn/sumproduct) and [statistics basics](/learn/statistics-basics).