Cellwise

Excel guide · 2 min read

How to calculate a weighted average in Excel

Multiply each value by its weight, add them up, and divide by the total weight: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). SUMPRODUCT does the multiplying and adding in one step.

Sample data

Every formula on this page was calculated live against this sheet.

ABCDEF
1ComponentScoreWeightProductUnits
2Homework920.212.5400
3Quizzes780.1511.8250
4Midterm850.2513.2150
5Final exam740.4

Method 1: SUMPRODUCT divided by SUM

A weighted average lets some values count more than others. In this course the final exam is worth 40% and quizzes only 15%, so the final should pull the grade much harder.

SUMPRODUCT(B2:B5,C2:C5) multiplies each score by its weight and adds the results: 92*0.2 + 78*0.15 + 85*0.25 + 74*0.4 = 80.95. Dividing by SUM(C2:C5) scales it by the total weight.

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

Result: 80.95

Returns 80.95, the weighted course grade.

Why AVERAGE gives a different answer

A plain AVERAGE treats every score equally. The student did well on homework and poorly on the heavily weighted final, so the simple average flatters them.

fx=AVERAGE(B2:B5)

Result: 82.25

Returns 82.25, higher than the true weighted grade of 80.95.

Weights that do not add up to 100%

Weights do not have to be percentages. They can be units, hours, credits or anything else. That is why you divide by SUM of the weights rather than assuming 1.

Here a shop bought the same product three times at different prices. Weighting each price by units bought gives the true average cost per unit. Columns E and F hold the price and units.

fx=SUMPRODUCT(E2:E4,F2:F4)/SUM(F2:F4)

Result: 12.4125

Returns 12.4125, about 12.41 per unit across 800 units.

Doing it step by step

If SUMPRODUCT feels opaque, use a helper column. It is longer but easy to check.

  1. In D2 type =B2*C2 and fill down to D5.
  2. Under it, add them up with =SUM(D2:D5).
  3. Divide that by =SUM(C2:C5) to get the weighted average.

Common mistakes

Ranges of different sizes

SUMPRODUCT returns #VALUE! if the two ranges have different numbers of rows. Make them match exactly, like B2:B5 and C2:C5.

Forgetting to divide by the weights

If your weights add to 1 (100%) the division changes nothing, but if they do not, the answer is wildly off. Always divide by SUM of the weights.

Weights stored as text

Text like "20%" typed with an apostrophe is ignored by SUMPRODUCT. Check the weights are right-aligned numbers.

FAQ

What is the weighted average formula in Excel?

=SUMPRODUCT(values,weights)/SUM(weights). There is no built-in WEIGHTED.AVERAGE function, so this pairing is the standard approach.

How do I calculate a weighted grade in Excel?

Put scores in one column and their percentage weights in the next, then use =SUMPRODUCT(scores,weights)/SUM(weights).

Can I use a weighted average with blank scores?

Blanks count as 0, which drags the result down. If some components are not graded yet, divide by only the weights of graded items, for example with SUMIF(B2:B5,"<>",C2:C5).

Can I calculate a weighted average with conditions?

Yes. Multiply in a condition with SUMPRODUCT, or use SUMIFS on a helper column of value times weight and divide by SUMIFS on the weights. For example, the average price for one supplier only.

Should my weights add up to 100%?

They do not have to. If they do, dividing by their sum leaves the answer unchanged. If they do not, such as units sold or credit hours, the division is what turns the total into a proper average.

Functions used

Keep learning

Updated 2026-10-31