The basic formula for weighted mean in Excel

A weighted mean is an average where some values count more than others. Instead of treating all numbers equally, you assign a weight to each one — usually a percentage or a multiplier — then calculate the average based on those weights. In Excel, you build this with the SUMPRODUCT function divided by the sum of the weights.

The formula is: =SUMPRODUCT(values, weights) / SUM(weights). SUMPRODUCT multiplies each value by its weight, then adds all those products together. You divide that total by the sum of all weights to get your weighted average.

For example, if you have test scores in cells A2:A4 and their weights in B2:B4, you would write =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). Excel multiplies each score by its weight, adds those results, then divides by the total weight.

Key Takeaways

  • SUMPRODUCT multiplies each value by its corresponding weight, then adds all the products together in one step.
  • Divide the SUMPRODUCT result by SUM(weights) to get the final weighted mean.
  • Weights do not have to add up to 1 or 100 — Excel handles any numbers as long as they represent relative importance.
  • You can use this same structure for grades, portfolio returns, survey results, or any situation where items have different importance.

Setting up your data in columns

Organize your spreadsheet with values in one column and their weights in an adjacent column. Put headers in the first row so you know what each column contains — for instance, "Score" and "Weight" or "Price" and "Quantity".

Make sure your data starts in the same row for both columns. If your values are in A2:A10, your weights should be in B2:B10. Mismatched ranges will give you an error or a wrong answer.

You can place the weighted mean formula in any empty cell below your data. Many people put it in the row right after their last data point, or in a cell to the right of the data with a label next to it.

A worked example with test grades

Suppose a student has three test scores and each test is worth a different percentage of the final grade. Test 1 (85 points) is worth 20%, Test 2 (92 points) is worth 30%, and Test 3 (78 points) is worth 50%.

Set it up like this:

TestScoreWeight
Test 1850.20
Test 2920.30
Test 3780.50

Put the scores in B2:B4 and the weights in C2:C4. In an empty cell, type =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Excel calculates (85×0.20) + (92×0.30) + (78×0.50) = 17 + 27.6 + 39 = 83.6. That is the weighted mean.

Using whole numbers as weights instead of percentages

You do not have to convert weights to decimals or percentages. You can use whole numbers that represent how many times each value should count. For instance, if you surveyed 50 people in one location and 30 in another, use 50 and 30 as your weights instead of converting to 0.625 and 0.375.

The formula stays the same: =SUMPRODUCT(values, weights) / SUM(weights). Excel divides by the sum of the weights automatically, so the final answer is correct whether your weights are decimals, percentages, or whole counts.

This approach is often clearer when you are working with real-world quantities like survey responses, inventory units, or transaction volumes.

Handling weights that do not add up to 100

If your weights do not sum to 1 or 100, the formula still works. The division by SUM(weights) normalizes the result for you. This is why SUMPRODUCT divided by SUM(weights) is more reliable than trying to multiply each value by a percentage manually.

For example, if you have weights of 2, 3, and 5 (which add to 10, not 1), the formula =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4) treats them as relative importance: the third item counts 2.5 times as much as the first. You get the same result as if you had used 0.2, 0.3, and 0.5.

Common mistakes to avoid

The most frequent error is forgetting to divide by SUM(weights). If you write only =SUMPRODUCT(values, weights) without the division, you get a number that is too large and meaningless. Always include the division step.

Another mistake is using mismatched ranges. If your values span A2:A10 but your weights only go to B8, Excel will either error or give you a partial result. Check that both ranges have the same number of cells and start in the same row.

Do not use AVERAGE with weights — the AVERAGE function ignores weights entirely and treats all values equally. SUMPRODUCT is the correct function for this task.

Frequently Asked Questions

What if I have missing data in one of my rows?

Leave the cell blank or enter 0. If you leave it blank, SUMPRODUCT treats it as 0, so that row contributes nothing to the sum. If you enter 0 explicitly, the result is the same. Make sure your weights are complete — a missing weight will cause an error.

Can I use this formula with negative numbers?

Yes. SUMPRODUCT handles negative values correctly. This is useful for calculating weighted returns on investments or weighted changes in a metric. The formula works the same way whether your values are positive, negative, or mixed.

Do my weights have to be percentages?

No. Weights can be any numbers that represent relative importance — percentages, decimals, whole counts, or even ratios. The formula divides by the sum of weights, so it normalizes automatically. Use whatever format makes sense for your data.

What is the difference between SUMPRODUCT and SUMIF for weighted calculations?

SUMPRODUCT multiplies two ranges together and sums the results, which is exactly what you need for a weighted mean. SUMIF adds values based on a condition in another column. For a straightforward weighted mean, SUMPRODUCT is simpler and more direct.

Can I use named ranges instead of cell references?

Yes. If you name your value range "Scores" and your weight range "Weights", you can write =SUMPRODUCT(Scores,Weights)/SUM(Weights). Named ranges make formulas easier to read and less prone to errors when you edit the spreadsheet later.