Weighted Average in Excel

A weighted average gives some values more importance than others. In Excel, multiply each value by its weight with SUMPRODUCT, then divide by the total of the weights.

fx=SUMPRODUCT(C3:C5,D3:D5)/SUM(D3:D5)
ProblemAn average where some values count more
Functions usedSUMPRODUCT, SUM
Works inAll Excel versions

Weighted marks

The final exam counts for 50% of the result, the mid-term for 30% and assignments for 20%:

=SUMPRODUCT(C3:C5,D3:D5)/SUM(D3:D5)
Excel SUMPRODUCT calculating a weighted average of marks

The weighted average is 76.1. A plain AVERAGE would give 75, because it treats all three marks the same.

How the formula works

  1. SUMPRODUCT multiplies each pair, 78×20%, 65×30% and 82×50%, and adds the results: 15.6 + 19.5 + 41 = 76.1.
  2. SUM(D3:D5) adds the weights: 100%.
  3. Dividing the two gives the weighted average. When the weights add up to 100%, the division changes nothing, but it keeps the formula right if they don't.

Average purchase price

Here the weights are the quantities, so a bigger purchase pulls the average towards its price:

=SUMPRODUCT(C3:C5,D3:D5)/SUM(C3:C5)
Excel weighted average price using quantities as weights

300 units cost 74,500 in total, an average of 248.33 each. Note that the formula divides by the quantities in C this time.

Which formula to use

To get Formula
Weights in a column =SUMPRODUCT(C3:C5,D3:D5)/SUM(D3:D5)
Weights typed in =SUMPRODUCT(C3:C5,{0.2,0.3,0.5})
Weighted average with a condition =SUMPRODUCT((B3:B9="Kochi")*C3:C9*D3:D9)/SUMIF(B3:B9,"Kochi",D3:D9)
Plain average, to compare =AVERAGE(C3:C5)

Notes

  • Both ranges must be the same size, or SUMPRODUCT returns #VALUE!.
  • Weights don't have to be percentages: credits, hours or quantities work the same way.
  • Text in a range counts as 0 in SUMPRODUCT.

Common errors

Problem Cause Fix
#VALUE! Ranges of different sizes Make both ranges the same rows
#DIV/0! All weights are 0 or blank Check the weight column
Result looks too high Dividing by COUNT instead of SUM of weights Divide by SUM of the weights

Practice file

Both examples with live formulas. Change a mark or weight to see the average move.

Download .xlsx

Related