How to / Sums and averages

How to calculate a weighted average in Excel

Use =SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4), with the values in column B and their weights in column C.

A final grade from weighted parts

ABCDE
1TaskScoreWeight
2Exam8050%81
3Project9030%
4Homework7020%

In E2: =SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4) → 81

80 × 50% + 90 × 30% + 70 × 20%. The plain average of the three scores is 80.

How it works

  1. SUMPRODUCT multiplies each score by its weight and adds the results.
  2. Dividing by the total of the weights makes the answer right whether the weights add up to 100%, to 1 or to anything else.
  3. A score with a large weight pulls the average toward itself.

Variations

  1. An average price, weighted by quantity

    ABCDE
    1ItemPriceQty
    2Pen1.23002.12
    3Notebook3.5100
    4Stapler920

    In E2: =SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4) → 2.12

    Most items sold are pens, so the average price per item is close to the price of a pen. The plain average of the three prices is 4.57.

  2. The plain average, for comparison

    ABCDE
    1TaskScoreWeight
    2Exam8050%80
    3Project9030%
    4Homework7020%

    In E2: =AVERAGE(B2:B4) → 80

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related