Weighted Average in Excel: SUMPRODUCT divided by SUM

A normal average treats every number the same. A weighted average lets some numbers count more — a final exam worth 50% should pull harder than a quiz worth 20%.

The formula

=SUMPRODUCT(values, weights)/SUM(weights)

Example: final grade

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

80×0.3 + 90×0.5 + 70×0.2 = 24 + 45 + 14 = 83. Divided by 1 (100%) = 83. The plain average would say 80.

Weights that do not add up to 100%

That is why we divide by SUM(weights). Credits 3, 4, 2 work exactly the same way — no need to convert to percentages.

Average price by quantity

You bought 100 units at ₹10 and 300 at ₹12. Average price paid:

=SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3)

(1,000 + 3,600) / 400 = ₹11.50, not ₹11.

The common mistake

Averaging percentages or rates directly (=AVERAGE(D2:D10) of conversion rates) gives each row equal say, even if one row had 10 visitors and another 10,000. Weight by the base: =SUMPRODUCT(D2:D10,C2:C10)/SUM(C2:C10).

How SUMPRODUCT works: SUMPRODUCT · plain average: AVERAGE