Calculate a Weighted Average

Excel & Google Sheets

How it works

SUMPRODUCT multiplies each value by its weight and adds the results — price × units for every row, summed. Dividing by the total of the weights turns that back into an average where heavy rows pull harder. With prices 10, 20, 30 and units 1, 3, 6, the weighted average is (10 + 60 + 180) ÷ 10 = 25 — versus a plain AVERAGE of 20, which pretends one unit of each was sold.

SUMPRODUCT(B2:B4,C2:C4)

Multiplies each value by its weight, then sums: value₁×weight₁ + value₂×weight₂ + …

/SUM(C2:C4)

Divides by the total weight, converting the weighted sum into an average.

When to use it

Use it whenever rows differ in importance: average selling price weighted by units, blended interest rates weighted by balance, assessment scores weighted by credit, cost per unit across warehouses.

Common mistakes

• ✕Using plain AVERAGE on per-row rates.

  ✓AVERAGE ignores volume, so small rows distort the result. If each row has a quantity, you almost always want the weighted version.

• ✕Value and weight ranges are different sizes.

  ✓SUMPRODUCT(B2:B4,C2:C9) returns #VALUE!. Both ranges must cover exactly the same rows.

• ✕Weights that sum to zero.

  ✓An empty weight column gives #DIV/0!. Guard it: =IF(SUM(C2:C4)=0,"",SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)).