How to Calculate a Weighted Average in Excel

A weighted average in Excel is an average where some values count more than others. You calculate it by multiplying each value by its weight, adding those results, and dividing by the total weight: =SUMPRODUCT(values,weights)/SUM(weights). This guide shows the formula on two real examples, course grades and average price paid, and explains why plain AVERAGE gets these wrong.

What is a weighted average?#

A weighted average gives each value a weight that says how much it counts. A normal average treats every value equally. A weighted average lets a final exam count four times as much as homework, or lets a large order count more than a small one.

The maths is simple:

1. Multiply each value by its weight.
2. Add up those results.
3. Divide by the total of the weights.

Excel has no WEIGHTED.AVERAGE function, so you build it from SUMPRODUCT and SUM. If you need a refresher on how SUM adds a range, see our Excel SUM function guide.

How to calculate a weighted average in Excel with SUMPRODUCT#

Here is a student's course grade, where each part of the course has a different weight:

Type the weights as 10%, 20% and so on, or as decimals (0.1, 0.2). Excel stores them the same way.

In F2, type:

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

What each part does:

• SUMPRODUCT(B2:B5,C2:C5) multiplies each score by its weight and adds them: 92×0.1 + 78×0.2 + 85×0.3 + 70×0.4 = 9.2 + 15.6 + 25.5 + 28 = 78.3.
• SUM(C2:C5) adds the weights: 0.1 + 0.2 + 0.3 + 0.4 = 1.
• The result is 78.3 ÷ 1 = 78.3.

Our guide to SUMPRODUCT explains in more detail how it multiplies row by row.

Why AVERAGE gives the wrong answer here#

AVERAGE gives every value the same weight, so it ignores how much each part of the course is worth.

=AVERAGE(B2:B5)

This returns 81.25, which is (92 + 78 + 85 + 70) ÷ 4. It is almost 3 points higher than the true grade of 78.3, because the low final exam score (70) carries 40% of the grade but only a quarter of the weight in AVERAGE.

Use AVERAGE when every item really counts the same, like the average of five test scores of equal value. Use a weighted average when the items have different importance or different sizes.

Weighted average when the weights don't add up to 100%#

Weights don't have to be percentages. They can be units, hours, or any measure of size. A common example is the average price you paid across several orders:

Here the units are the weights. The big order of 400 units should pull the average toward 2.10.

=SUMPRODUCT(B9:B11,C9:C11)/SUM(B9:B11)

SUMPRODUCT gives the total cost: 250 + 840 + 150 = 1,240. SUM gives the total units: 550. The weighted average price is 1,240 ÷ 550 = 2.2545..., about 2.25 per unit. Plain AVERAGE of the prices would say 2.53, which overstates what you really paid.

This is why you always divide by SUM(weights). When the weights happen to add up to 1 (100%), the division changes nothing. When they don't, it is the step that turns a weighted total into an average. To show two decimal places, wrap it: =ROUND(SUMPRODUCT(B9:B11,C9:C11)/SUM(B9:B11),2) returns 2.25.

How to do a weighted average without SUMPRODUCT#

You can also use a helper column, which some people find easier to check.

Step 1: Multiply each value by its weight#

In D2, type =B2*C2 and fill down to D5 with Ctrl+D (Windows) or ⌘D (Mac). Column D now shows 9.2, 15.6, 25.5 and 28.

Step 2: Divide the total by the total weight#

In F2, type:

=SUM(D2:D5)/SUM(C2:C5)

The result is again 78.3. The helper column shows each step, which is handy when you have to explain your work in a test. SUMPRODUCT does the same job in one cell and leaves the sheet tidier. Both are correct.

Common weighted average mistakes#

These are the errors that come up most often:

• Dividing by the number of items. =SUMPRODUCT(B2:B5,C2:C5)/4 is wrong unless every weight is 1. Always divide by the sum of the weights.
• Forgetting to divide at all. With weights that are units, the result is a total cost, not an average. If your "average" is far bigger than every value, this is the cause.
• Ranges of different sizes. =SUMPRODUCT(B2:B5,C2:C6) returns #VALUE!. Both ranges must cover the same rows.
• Weights typed as whole numbers by mistake. Typing 10 instead of 10% makes the weights add to 100, not 1. The formula still works if you divide by SUM, which is one more reason to always include it.
• Blank weights. An empty weight counts as 0, so that value is ignored. That may be right (a test not taken yet) or a data gap: check before you report.

When you copy a weighted average down to other rows, lock the weights range with dollar signs, for example $C$2:$C$5, so it doesn't shift.

Where weighted averages are used at work#

Weighted averages appear wherever items have different sizes: average price per unit in purchasing, average salary across teams of different headcounts, scores in a supplier or performance review, and blended interest or commission rates. In job tests, a typical task gives you scores and weights and asks for the overall result, so expect the SUMPRODUCT/SUM pattern. The Excel skills for admin jobs guide lists other calculations that appear in office tests.

Two related calculations come up alongside this one: a running total that grows row by row, and MOD and INT for remainders and whole numbers. If formulas are still new to you, start with your first Excel formula.

Practise it#

Try the live exercise on this page to build the weighted grade yourself. Then practise AVERAGE, MIN and MAX in a live spreadsheet to see how a plain average behaves, and use the SUM and AutoSum lesson to get fast at selecting the ranges these formulas need.