A weighted average gives some values more influence than others, which makes it useful for grades, prices, survey results, and performance metrics.
In Excel, you can calculate one with a compact formula, but the result depends on matching each value to its correct weight.
What is a weighted average?
A weighted average multiplies each value by its weight, adds those products, then divides by the sum of the weights.
Unlike a simple average, it accounts for how much each item should contribute.
For example, if a course grade includes homework worth 30% and a final exam worth 70%, the exam has more influence on the overall grade.
The general formula is:
Weighted average = SUM(value × weight) / SUM(weights)
Weights can be percentages, quantities, credits, or any other measures of relative importance.
They do not have to add up to 100 when you divide by their total.
Calculate a weighted average with SUMPRODUCT
SUMPRODUCT multiplies corresponding entries in two ranges and adds the products.
Combine it with SUM to divide by the total weight.
Suppose values are in cells B2:B4 and their weights are in C2:C4:
B2: 80, weight inC2: 20B3: 90, weight inC3: 30B4: 70, weight inC4: 50
Enter this formula in another cell:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
The calculation is (80×20 + 90×30 + 70×50) / (20+30+50), which equals 78.
The value 70 contributes most because it has the largest weight.
This formula works whether the weights are entered as 20, 30, and 50 or as 20%, 30%, and 50%.
In either case, the numerator and denominator use the same weight scale.
Use percentage weights
If the weights are percentages and add up to 100%, you can omit the denominator.
With values in B2:B4 and percentage weights in C2:C4, use:
=SUMPRODUCT(B2:B4,C2:C4)
For values 80, 90, and 70 with weights 20%, 30%, and 50%, the result is 78.
If the percentages do not total 100%—perhaps because one component is missing—divide by their sum to normalize the result:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
Calculate a weighted average with AVERAGE.WEIGHTED
In newer versions of Excel, AVERAGE.WEIGHTED provides a direct alternative.
The corresponding formula is:
=AVERAGE.WEIGHTED(B2:B4,C2:C4)
It returns a weighted average of the values in B2:B4 using the weights in C2:C4.
Availability can vary by Excel version and update channel.
If Excel displays #NAME?, your version may not support the function; use SUMPRODUCT divided by SUM, which is widely compatible with desktop Excel and Excel for the web.
Calculate a weighted average from quantities
Weights are often quantities rather than percentages.
For example, if a product sold for different prices in different quantities, calculate the average selling price per unit with the same method.
Put prices in B2:B4 and units sold in C2:C4, then enter:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)
If the prices are $10, $12, and $15, and the quantities are 5, 20, and 5, the weighted average price is $12.17 when rounded to two decimal places.
This differs from the simple average of the three prices because more units sold at $12.
Handle blank cells and zero weights
With the SUMPRODUCT formula, a blank value cell generally contributes zero to the numerator, while its weight still contributes to the denominator.
If a value is missing and should be excluded entirely, exclude its weight too.
For example, if the third value is missing, calculate over B2:B3 and C2:C3 instead of including C4.
A zero weight means the corresponding value has no effect on the result.
It is safe to include when other weights total more than zero.
If all weights are zero—or the weight range sums to zero—the division has no valid result and Excel returns #DIV/0!.
You can guard against this case:
=IF(SUM(C2:C4)=0,"No positive total weight",SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4))
Choose the message that suits your spreadsheet, or return a blank string with "" if that is preferable.
Check range sizes and data types
The values and weights must align row by row.
If a value range has three cells, its weight range must also have three cells in the same order.
Mismatched dimensions can cause #VALUE! or an invalid calculation, depending on the formula and Excel version.
Make sure numbers are stored as numbers, not text.
A cell containing the text "20" may not behave like the numeric value 20.
Check imported data, leading apostrophes, and decimal separators when results seem wrong.
Also check for errors such as #N/A in either range: SUMPRODUCT will typically propagate an error rather than ignore it.
Use criteria for a conditional weighted average
To calculate a weighted average for only one category, filter the numerator and denominator to the same rows.
For example, if categories are in A2:A10, values are in B2:B10, and weights are in C2:C10, this formula calculates the average for the category named in E2:
=SUMPRODUCT((A2:A10=E2)*B2:B10*C2:C10)/SUMIF(A2:A10,E2,C2:C10)
The condition selects matching rows for the weighted sum, while SUMIF totals the weights for that category.
If no rows match, the denominator is zero, so add an IF check if the formula will encounter missing categories.
Why does my weighted average look wrong?
- Weights are reversed or misaligned: Confirm each weight sits beside the value it belongs to.
- You used a simple average:
AVERAGE(B2:B4)treats every value equally and ignores weights. - Percentages do not total 100%: Divide by
SUM(C2:C4)to normalize the result. - A missing item still has a weight: Remove that row from both ranges or set its weight to zero.
- The denominator is zero: Check for blank or zero weights. The formulas here assume nonnegative weights; if your application allows negative weights, define how those should be handled.
- Displayed precision differs from the stored value: Apply number formatting to control decimal places; formatting changes how a result appears, not the underlying calculation.
For most spreadsheets, =SUMPRODUCT(values,weights)/SUM(weights) is the clearest general-purpose approach.
Use ranges of equal length, keep each value paired with its intended weight, and decide explicitly how the sheet should treat missing data.