Weighted Average

Excel Formulas › Average

All versionsSUMPRODUCT

When some values count more than others — a course grade where the final exam is worth more than homework, a portfolio return by position size — a plain AVERAGE is wrong. A weighted average with SUMPRODUCT gives each value its proper pull.

Quick formula: for values in B2:B5 and their weights in C2:C5:
=SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5)
SUMPRODUCT multiplies each value by its weight and adds the results; dividing by the total weight gives the weighted mean.

Functions used (tap for the full reference guide):

SUMPRODUCTSUM

The example

A course grade: each component has a score and a weight.

ABC
1ComponentScoreWeight
2Homework8020%
3Midterm7530%
4Final9050%
5Weighted grade:83.5

The formula

The weighted grade:

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4) // (80×.2 + 75×.3 + 90×.5) / 1 = 83.5

How it works

SUMPRODUCT does the multiply-and-add in one step:

  1. SUMPRODUCT(B2:B4, C2:C4) multiplies each score by its weight and sums: 16 + 22.5 + 45 = 83.5.
  2. Dividing by SUM(C2:C4) (the total weight) normalizes the result. Here the weights total 100%, so dividing by 1 leaves 83.5.
  3. If your weights don’t add to 1 (e.g. they’re raw points like 2, 3, 5), the division is what makes the average correct.

Try it: interactive demo

Live demo

Set each score; weights are 20% / 30% / 50%. See the weighted grade vs the plain average.

Weighted:   plain average:

Variations

Weights as raw points

Works the same when weights are 2/3/5 instead of percentages — the SUM divisor handles it:

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)

Weighted average price across orders

Average price weighted by quantity sold:

=SUMPRODUCT(price, qty) / SUM(qty)

Pitfalls & errors

#VALUE! The value and weight ranges are different sizes. SUMPRODUCT needs them to line up one-to-one.

Don’t forget to divide. =SUMPRODUCT(values, weights) alone is a weighted total, not an average. Divide by the sum of the weights.

Blank weights skew the result. A missing weight counts as 0 in SUMPRODUCT but the SUM still excludes it — make sure every value has a weight.

Practice workbook

📊
Download the free Weighted Average practice workbook
The grade example with live SUMPRODUCT weighted average and the raw-points variant, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a weighted average in Excel?
Use =SUMPRODUCT(values, weights)/SUM(weights). SUMPRODUCT multiplies each value by its weight and totals them, and dividing by the total weight gives the weighted mean.
What's the difference between AVERAGE and a weighted average?
AVERAGE treats every value equally. A weighted average lets some values count more, e.g. a final exam worth 50% versus homework worth 20%, by multiplying each by its weight before averaging.
Do the weights have to add up to 100%?
No. Because the formula divides by SUM(weights), you can use raw points like 2, 3, 5 and still get the correct weighted average.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Average by group · Sum the top N values · Percent of total

Function references: SUMPRODUCT · SUM