Weighted Average in Excel: Formula and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

A weighted average in Excel multiplies each value by its weight, adds the products, then divides by the total of the weights. You build it with two functions: SUMPRODUCT for the top of the fraction and SUM for the bottom. A plain AVERAGE cannot do this because it treats every value as equally important.

Quick Answer

The formula is =SUMPRODUCT(values, weights)/SUM(weights).
SUMPRODUCT multiplies each value by its matching weight and adds the results in one step.
SUM adds the weights so the result is scaled back to the original units.
Weights can be decimals that add to 1, counts, hours, or any positive numbers.
Use AVERAGE only when every value should count equally, which is a different question.

The Formula

The weighted mean is the sum of each value times its weight, divided by the sum of the weights.

$$\bar{x}_w = \frac{\sum_{i=1}^{n} w_i x_i}{\sum_{i=1}^{n} w_i}$$

Each symbol means the following.

Symbol	Meaning
$x_i$	The i-th value you are averaging
$w_i$	The weight assigned to that value
$n$	The number of value-weight pairs
$\sum w_i x_i$	The sum of every value multiplied by its weight
$\sum w_i$	The sum of all weights

When the weights already add to 1, the denominator is 1 and the weighted average is just the sum of the products. When weights are counts, such as units purchased or hours worked, the denominator converts the total back into a per-unit figure. Microsoft documents this exact pattern, dividing the total cost of all orders by the total number of units ordered [1].

How to Calculate It Step by Step

Put your values in one column and their weights in the column next to it. Keep each weight on the same row as the value it belongs to.
Check that the two ranges are the same size. If values are in B2:B4, weights must be in C2:C4.
In an empty cell, type =SUMPRODUCT( and select the value range, then a comma, then the weight range, then close the parenthesis.
Divide by the sum of the weights by typing /SUM( and selecting the weight range again.
Press Enter. The result is the weighted average.
Compare it with =AVERAGE(values) to see how much the weights shift the answer.

If you want to see the individual products, add a helper column with =B2*C2 and fill it down. That column makes the arithmetic visible, which helps when you are checking your work. The same multiplication logic appears in other aggregate functions, and the Excel SUM function guide covers how SUM handles ranges and mixed references.

Worked Example

Three students took an exam, and each score carries a different weight toward the final grade.

	A	B	C	D
1	Student	Score	Weight	Weighted Score
2	Ana	80	0.2	`=B2*C2` -> displays 16.00
3	Ben	90	0.3	`=B3*C3` -> displays 27.00
4	Cara	70	0.5	`=B4*C4` -> displays 35.00
5	Weighted Average	`=SUMPRODUCT(B2:B4,C2:C4)` -> displays 78.00
6	Unweighted Average	`=AVERAGE(B2:B4)` -> displays 80.00

Cell B5 multiplies each score by its weight and sums the results to get the weighted average. Cell B6 calculates the plain unweighted average of the three scores for comparison.

The weighted average is 78.00 and the unweighted average is 80.00. Cara's score of 70 carries half the total weight, so it pulls the result down even though Ana and Ben scored higher. The helper column confirms the arithmetic: 16.00 plus 27.00 plus 35.00 equals 78.00, and the weights 0.2, 0.3, and 0.5 add to 1.

How to Interpret the Result

The weighted average tells you the central value once each observation counts in proportion to its weight. In the example, 78.00 is the grade that reflects the course structure, while 80.00 is what you would get if all three exams mattered equally.

A weighted average always sits between the smallest and largest values in your data. It moves toward whichever values carry the most weight. If a single weight dominates, the result will be close to that value.

Compare the weighted and unweighted figures to understand the effect of your weighting. A large gap means the weights are doing real work. A tiny gap means the weights are nearly equal, and a plain average would have been fine. For a fuller treatment of the underlying statistic, see the weighted arithmetic mean formula and examples.

Doing It in Software

Excel offers the most direct route. The formula =SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:C4) is wrong because SUM(B2:C4) adds the scores as well as the weights, so always pass only the weight range to SUM. Microsoft's own example uses =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) to divide total cost by total units [1].

If you prefer a manual check, build the helper column and use =SUM(D2:D4)/SUM(C2:C4). Both approaches return the same number.

In R, the built-in weighted.mean() function does the job in one call. Pass the values first and the weights second, as in weighted.mean(x, w). In Python, NumPy provides numpy.average(values, weights=weights), which computes the same quantity. Both functions divide by the sum of the weights by default, matching the Excel formula.

For quick checks without a spreadsheet, the Mean, Median & Mode Calculator handles simple averages and helps you see how a weighted result differs from an unweighted one.

Common Mistakes

Using AVERAGE on the values and weights together. AVERAGE treats every cell equally, so it cannot weight anything. Fix it by switching to SUMPRODUCT divided by SUM.
Mismatched range sizes. If values are in B2:B10 and weights in C2:C9, the formula returns an error or a wrong number. Fix it by making both ranges cover the same rows.
Forgetting to divide by the sum of weights. =SUMPRODUCT(values, weights) alone gives the weighted total, not the average. Fix it by adding /SUM(weights).
Weights that do not add to 1 when you assume they do. If you skip the division because you think the weights total 1, verify it first. Fix it by keeping the division in the formula, which is harmless when the weights do sum to 1.
Text or blank cells inside a range. SUMPRODUCT treats text as zero, which silently shrinks the numerator while SUM may still count the weight. Fix it by cleaning the ranges or using SUMIF and SUMIFS to include only valid rows, as covered in the SUMIF and SUMIFS guide.
Negative or zero weights. A zero weight removes a value from the average, and a negative weight can push the result outside the data range. Fix it by confirming each weight reflects a real share or count.

Limitations

A weighted average only makes sense when the weights represent something meaningful, such as importance, frequency, or quantity. If you assign weights arbitrarily, the result is arbitrary too. The method also assumes each value is a single number. It cannot summarize a distribution, so it hides spread, outliers, and skew that a chart or a standard deviation would reveal.

Weighted averages are sensitive to extreme weights. One very large weight can dominate the result and make the other values nearly irrelevant. They also cannot fix bad data. If a value or weight is entered incorrectly, the weighted average will be wrong in a way that is hard to spot, because the formula returns a single number with no warning. Always inspect the inputs before trusting the output.

Frequently Asked Questions

What is the difference between a weighted average and a normal average?

A normal average adds all values and divides by how many there are, so every value counts the same. A weighted average multiplies each value by a weight first, then divides by the total weight. The two give the same answer only when all weights are equal.

Can I calculate a weighted average in Excel without SUMPRODUCT?

Yes. Add a helper column with =B2*C2 filled down, then use =SUM(D2:D4)/SUM(C2:C4). This produces the same result and lets you see each product. The Excel formulas cheat sheet lists other functions that pair well with this approach.

What if my weights add up to 100 instead of 1?

Nothing changes. The formula divides by the sum of the weights, so weights of 20, 30, and 50 give the same answer as 0.2, 0.3, and 0.5. Percentages and decimals are interchangeable here.

Why does my weighted average fall outside the range of my values?

This usually means a weight is negative, or a value and its weight are on different rows. Check that each weight sits beside the value it belongs to and that no weight is below zero. A correct weighted average always lands between the smallest and largest values.

How do I round the weighted average in Excel?

Wrap the formula in ROUND, as in =ROUND(SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4),2). The second argument sets the number of decimal places. The Excel ROUND function guide explains the syntax and how it differs from changing cell formatting.

References

Calculate the average of a group of numbers | Microsoft Support