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.
How Weighted Average works
Availability: SUMPRODUCT and SUM are in every version of Excel, Excel for the web and Google Sheets. Excel has no dedicated weighted-average function; Google Sheets adds AVERAGE.WEIGHTED, which does the same job in one call but does not survive a conversion to .xlsx.
A weighted average lets some rows count for more than others. SUMPRODUCT multiplies each value by its weight and adds the products; dividing that total by the sum of the weights converts it back into an average. AVERAGE cannot do this - it gives every row the same say - which is why a plain average of unit prices across purchase lots, or of branch satisfaction scores, is usually the wrong number to publish.
The weights are whatever makes one row matter more: credit hours behind a grade, units bought at each price, headcount behind a branch score, days at each rate. They do not have to be percentages and they do not have to add up to anything in particular. Dividing by SUM(weights) normalises them whatever their scale, so counts, hours and percentages all work without conversion. The division is only redundant when the weights already total exactly 1 or 100%, and leaving it in costs nothing while surviving somebody later editing a weight.
The two ranges have to be the same shape and the same length, or SUMPRODUCT returns #VALUE!. B2:B10 against C2:C11 fails even though it reads correctly at a glance, and a single inserted row is the usual cause. In this comma-separated form SUMPRODUCT treats text and blanks as zero rather than erroring, which cuts both ways: a missing weight harmlessly removes that row from both halves of the calculation, but a missing value keeps its weight in the denominator and pulls the answer down.
The same pattern extends to conditional weighted averages. Multiplying the arrays together instead of listing them as separate arguments - =SUMPRODUCT((A2:A100="West")*B2:B100*C2:C100)/SUMPRODUCT((A2:A100="West")*C2:C100) - restricts both the numerator and the denominator to matching rows. Note that with the multiplication form, text anywhere in the ranges produces #VALUE! rather than being treated as zero.
Syntax
=SUMPRODUCT(values_range, weights_range)/SUM(weights_range)| Argument | Required | What it does |
|---|---|---|
array1 (values) | Required | The numbers being averaged: prices, scores, rates. Must be the same size and orientation as the weights. |
array2 (weights) | Required | How much each value counts: quantities, credit hours, headcount, percentages. Lines up row for row with the values. |
array3, ... array255 | Optional | SUMPRODUCT accepts up to 255 arrays and multiplies them element by element. A third array is how you add a condition, for example (A2:A100="West") as a TRUE/FALSE array that zeroes out non-matching rows. |
number1 (the SUM divisor) | Required | The weights range again, this time totalled. This is what turns the sum of products back into an average, and it must cover exactly the same rows as the weights inside SUMPRODUCT. |
More worked examples
A portfolio: holding values in B2:B5 are 12,000, 8,000, 5,000 and 25,000; the year's returns in C2:C5 are 4%, -2%, 7% and 3.5%.
=SUMPRODUCT(C2:C5, B2:B5)/SUM(B2:B5)Returns: Returns 0.0309, or 3.09% when formatted as a percentage. The plain =AVERAGE(C2:C5) returns 3.13%, because it lets the 5,000 holding count as much as the 25,000 one.
Three purchase lots of the same component: prices in B2:B4 of 2.50, 2.80 and 3.10, quantities in C2:C4 of 100, 400 and 500.
=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4)Returns: Returns 2.92. The products are 250, 1,120 and 1,550, totalling 2,920 across 1,000 units. =AVERAGE(B2:B4) returns 2.80 and understates the true cost per unit.
This is the figure to carry into a margin calculation; the simple average of the three prices is not.
A weighted average of the same prices restricted to the lots from one supplier, whose name sits in column A.
=SUMPRODUCT((A2:A4="Acme")*B2:B4*C2:C4)/SUMPRODUCT((A2:A4="Acme")*C2:C4)Returns: With only the second and third lots from Acme, returns 2.967 - 2,670 across 900 units.
Both halves need the same condition. Applying it only to the top produces a number that is not an average of anything.
Common mistakes
- #VALUE! from ranges of different lengths
- SUMPRODUCT requires the arrays to have identical dimensions. B2:B10 with C2:C11, or a column range paired with a row range, returns #VALUE!. After inserting or deleting rows, check both range ends - including the one inside SUM, which is easy to forget.
- Forgetting the divisor
- =SUMPRODUCT(B2:B10, C2:C10) on its own is a weighted total, not an average, and when the weights are quantities the number comes out orders of magnitude too large. It is only correct without the division when the weights already sum to exactly 1.
- A value stored as text pulls the average down
- In the comma form, text counts as zero. A price of 2.80 imported as text still carries its 400-unit weight into the denominator but contributes nothing to the numerator, so the answer falls without anything erroring. Convert the values column to real numbers and confirm with COUNT against COUNTA.
- Weights that cancel out
- Weights that include negatives - refunds entered as negative quantities, adjustments booked against a period - can make SUM(weights) zero, which gives #DIV/0!, or near zero, which gives a wildly inflated result. Net the underlying rows out before weighting rather than relying on the arithmetic to do it.
- Averaging averages
- Taking the mean of four class averages, or four branch scores, is the same mistake in a different disguise: each average already represents a different number of people. Keep the group sizes in a column and weight by them, or the small groups will dominate the answer.
Frequently Asked Questions
Because AVERAGE gives every row the same weight of 1. The two agree only when all your weights are equal. The larger the spread in the weights, the further apart the answers get, which is precisely why the weighted version is worth building.
No. Dividing by SUM(weights) normalises whatever scale they are on, so raw counts, hours or headcounts work directly. Weights that already total 1 make the divisor equal to 1, which is why the division can look redundant in textbook examples - but leaving it in protects you when someone edits a weight later.
Multiply a condition into both halves: =SUMPRODUCT((A2:A100="West")*B2:B100*C2:C100)/SUMPRODUCT((A2:A100="West")*C2:C100). The comparison produces TRUE/FALSE, which Excel treats as 1 and 0 during multiplication, so non-matching rows contribute nothing to either the total or the divisor.
Two things. Either the ranges are different sizes, which is the common case, or you are using the multiplication form and one of the ranges contains text - unlike the comma form, multiplication cannot coerce text to zero. Check the range ends first, then look for stray text in the data.
No. Google Sheets offers AVERAGE.WEIGHTED(values, weights), but Excel has never added an equivalent, and a Sheets file using it converts to .xlsx with the function unresolved. The SUMPRODUCT and SUM construction is the portable version and works identically in both.
Related Tools
SUMPRODUCT Generator
The underlying function, including its use for conditional sums and counts.
AVERAGEIF Generator
An unweighted average restricted to rows that meet a condition.
STDEV.S Generator
How spread out the underlying values are, which a single average never shows.
MEDIAN Generator
A centre point that a handful of extreme rows cannot drag around.