How to calculate a weighted average in Excel (SUMPRODUCT)

A plain AVERAGE treats every row as equally important. A weighted average lets the bigger or more important rows pull the result more — which is what you actually want when averaging prices across different quantities, grades across different credit weights, or rates across different balances. This guide gives you the one-line SUMPRODUCT/SUM formula, a worked example, and the pitfalls to avoid.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

1.What a weighted average is

A weighted average is an average where each value carries a weight — a number that says how much that value should count. A plain average is just a special case where every weight is equal to 1. The moment the rows differ in size or importance, the plain average misleads, and the weighted average is the honest figure.

The classic example is price. If you bought 100 units at £2 and 10 units at £5, the plain average of the two prices is £3.50 — but you barely bought any of the expensive ones, so your true average cost is much closer to £2. Weighting each price by the quantity bought gives the right answer. The same logic applies to a portfolio yield (weight by holding size), a course GPA (weight by credit hours), or a blended discount rate (weight by spend).

Excel has no single WEIGHTEDAVERAGE function, but SUMPRODUCT divided by SUM does the job cleanly in one cell — no helper column required.

2.Step 1 — Lay out values and weights

Open Excel. Put your values in one column and the matching weights in the column beside them, row for row. The two ranges must be the same length and aligned — SUMPRODUCT pairs them up by position.

3.Step 2 — SUMPRODUCT

SUMPRODUCT takes two (or more) ranges, multiplies them element by element, and adds up the results — all in one function call. For the numerator of a weighted average:

=SUMPRODUCT(B2:B6, C2:C6)

Behind the scenes this computes 2.00×100 + 3.50×40 + 5.00×10 + 2.80×60 + 4.20×20 = 642. That is the total value — here, the total amount spent. It is correct but it is a grand total, not an average yet; we fix that in the next step.

The beauty of SUMPRODUCT is that you never need a separate “price × quantity” helper column. It does the row-by-row multiplication and the addition together.

4.Step 3 — Divide by the total weight

To turn the weighted total into a weighted average, divide by the sum of the weights:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

The denominator SUM(C2:C6) = 100+40+10+60+20 = 230. So the weighted average price is 642 / 230 = 2.79. Notice that is well below the plain average of the five prices (which is £3.50): the cheap, high-volume Product A dominates because it carries the most weight. That gap is exactly why the weighted average is the figure you want.

5.Step 4 — When to use it

Reach for a weighted average any time the rows are not interchangeable:

• Average price or cost — weight by quantity bought or sold.
• Blended interest rate or yield — weight by balance or holding size.
• Course grade / GPA — weight by credit hours.
• Customer satisfaction across segments — weight by segment size.
• Average margin across products — weight by revenue.

The tell is always the same: if averaging two numbers of very different size as equals feels wrong, you need weights. When the rows really are equal — five test scores from the same student, say — a plain AVERAGE is correct and simpler.

Worked example

A buyer wants the true average cost per unit across five purchase batches (illustrative figures):

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)   →   642 / 230   →   2.79

The weighted average cost is £2.79 per unit. The plain =AVERAGE(B2:B6) would have returned £3.50 — a full 71p higher — because it ignores the fact that the cheapest batch was also by far the largest. On a 230-unit purchase that 71p error is £163 of phantom cost. The weighted average is the number that reconciles back to what was actually spent.

Common mistakes

1. Forgetting to divide by SUM(weights). SUMPRODUCT on its own gives a grand total, not an average. Without the denominator you get 642, not 2.79.

2. Misaligned ranges. The values and weights must be the same length and in the same order. If B2:B6 has five rows but C2:C7 has six, SUMPRODUCT returns a #VALUE! error.

3. Blank or text cells in a range. A stray text entry in the weights column will error or be treated as zero. Clean the ranges, or wrap values in N() if you have mixed content.

4. Using a plain AVERAGE out of habit. The most common error isn’t a formula bug — it’s reaching for AVERAGE when the rows clearly differ in size. Always ask whether the items are really equal before averaging them.

The 30-second shortcut

Weighted averages crop up everywhere in reporting — blended margins, average cost, segment-weighted scores. DataHub Pro computes them across your data automatically, alongside trends and forecasts, so you don’t have to remember to weight by the right column each time.

Frequently asked questions