Weighted Average Calculator
Build a SUMPRODUCT formula that averages values by their weights instead of treating every row equally.
Weighted Average
Build a SUMPRODUCT formula that averages values by their weights instead of treating every row equally.
The numbers being averaged, such as grades, prices, or scores.
How much each value counts: credit hours, units sold, headcount, or percentages. Must line up row for row with the values. This range is used twice in the formula, inside SUMPRODUCT and inside SUM.
=SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10)Worked Example
Three exam scores in B2:B4 (90, 85, 70) weighted at 20%, 30%, and 50% in C2:C4.
=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4)Returns: Returns 78.5. SUMPRODUCT gives 18 + 25.5 + 35 = 78.5, and SUM of the weights is 1, so the division leaves the total unchanged.
Checks Before You Paste
- •The two ranges must have the same shape and length or SUMPRODUCT returns #VALUE!. B2:B10 paired with C2:C11 fails even though it looks right at a glance, and a single inserted row is the usual cause.
- •Dividing by SUM(weights) is what turns the total into an average. You can drop it only when the weights already add to exactly 1 or 100%, and keeping it costs nothing while surviving someone later editing a weight.
- •In this comma-separated form SUMPRODUCT treats blanks and text as 0 instead of erroring. A missing weight harmlessly drops that row out of both halves, but a missing value keeps its weight in the denominator and drags the answer down, so exclude incomplete rows rather than leaving them blank.