The basic formula for a weighted average

A weighted average in Excel uses the SUMPRODUCT function paired with SUM. The formula is: =SUMPRODUCT(values, weights)/SUM(weights). This multiplies each value by its weight, adds those products together, then divides by the sum of all weights.

For example, if you have test scores in cells A2:A4 (85, 90, 78) and their weights in B2:B4 (0.3, 0.5, 0.2), you would write =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4). Excel multiplies 85 by 0.3, 90 by 0.5, and 78 by 0.2, adds those products (25.5 + 45 + 15.6 = 86.1), then divides by the total weight (1.0), giving you 86.1.

Key Takeaways

  • SUMPRODUCT multiplies each value by its weight, then SUM divides by the total weight to get your weighted average.
  • Weights can be percentages (0.3, 0.5, 0.2), whole numbers (3, 5, 2), or any numbers that represent relative importance.
  • The formula works the same way whether weights add up to 1, 100, or any other total — SUMPRODUCT handles the math automatically.
  • You can use this method for grades, project scores, inventory costs, or any situation where some items matter more than others.

When you need weights instead of a straightforward average

A straightforward average treats all numbers equally. A weighted average gives more influence to numbers that matter more. If a final exam counts for 40% of your grade and a quiz counts for 10%, the exam score should pull the average more than the quiz score does.

Use a weighted average whenever the items you are averaging have different importance, frequency, or size. A store manager averaging sales per location needs to weight by store size. A teacher averaging assignment scores needs to weight by how much each assignment counts toward the final grade. A manufacturer averaging defect rates needs to weight by production volume.

Setting up your data in Excel

Organize your spreadsheet with values in one column and weights in another. Put a header in row 1 so you know what each column contains. Put the actual numbers starting in row 2. This makes the formula easier to read and easier to update later.

For instance, if you are tracking student grades, put "Assignment" in A1 and "Weight" in B1. Then put each grade in column A (A2, A3, A4) and each weight in column B (B2, B3, B4). The weights can be decimals (0.2, 0.3, 0.5) or percentages written as decimals, or whole numbers (20, 30, 50) — Excel will calculate the same result either way because SUMPRODUCT divides by the sum of weights.

How SUMPRODUCT actually works

SUMPRODUCT takes two ranges and multiplies them pair by pair, then adds all the products. If you write =SUMPRODUCT(A2:A4,B2:B4), Excel does this: (A2 × B2) + (A3 × B3) + (A4 × B4). The result is a single number — the sum of all those products.

Then you divide that sum by SUM(B2:B4), which adds up all the weights. This division is what makes it a weighted average instead of just a weighted sum. If your weights add up to 1 (like 0.2 + 0.3 + 0.5), the division barely changes the number. If your weights add up to 100 (like 20 + 30 + 50), the division scales it down to the right size.

Handling weights that don't add up to 1 or 100

Your weights do not have to add up to any particular number. You might weight items by how many times they occurred (5 times, 3 times, 2 times) or by their size (500 units, 300 units, 200 units). The formula =SUMPRODUCT(values,weights)/SUM(weights) works the same way.

If you have sales figures of $1,200, $800, and $600 weighted by store size of 50, 30, and 20 employees, the formula calculates: (1200 × 50 + 800 × 30 + 600 × 20) / (50 + 30 + 20) = (60,000 + 24,000 + 12,000) / 100 = 96,000 / 100 = 960. The weights add up to 100, but they could add up to 10 or 1,000 and the answer would still be correct because the division adjusts for the total.

Common mistakes and how to fix them

The most common mistake is forgetting to divide by the sum of weights. If you write only =SUMPRODUCT(A2:A4,B2:B4) without the /SUM(B2:B4) part, you get a weighted sum, not a weighted average. The number will be much larger than it should be.

Another mistake is using the wrong range. If your data starts in row 2 and ends in row 10, make sure both the values range and the weights range go from row 2 to row 10. Mismatched ranges (like A2:A10 and B2:B9) will give you the wrong answer because Excel pairs up the wrong numbers.

A third mistake is mixing up which column is values and which is weights. The order matters: =SUMPRODUCT(values,weights) is not the same as =SUMPRODUCT(weights,values) if the numbers are different. Write out what each column represents so you do not swap them by accident.

Using weighted averages for grades, costs, and other real situations

In a classroom, a teacher might have homework (20% of grade), quizzes (30%), and a final exam (50%). If a student scores 92 on homework, 88 on quizzes, and 85 on the exam, the weighted average is =SUMPRODUCT(92,88,85; 0.2,0.3,0.5)/SUM(0.2,0.3,0.5) = (18.4 + 26.4 + 42.5) / 1 = 87.3. The exam pulls the grade down because it has the highest weight.

In inventory management, a warehouse might stock three products at different prices and quantities. Product A costs $10 and you have 100 units, Product B costs $15 and you have 50 units, Product C costs $20 and you have 30 units. The weighted average cost per unit is =SUMPRODUCT(10,15,20; 100,50,30)/SUM(100,50,30) = (1000 + 750 + 600) / 180 = 12.50. This tells you the true average cost of your inventory, not just the average of the three prices.

Frequently Asked Questions

Can I use percentages as weights instead of decimals?

Yes. If your weights are 20%, 30%, and 50%, you can enter them as 20, 30, 50 or as 0.2, 0.3, 0.5. The formula divides by the sum of weights, so it adjusts automatically. Both methods give the same answer.

What if some of my weights are zero?

That is fine. A weight of zero means that item does not count toward the average at all. If you have four test scores but only three of them count, set the fourth weight to 0. The formula will ignore it.

Can I use AVERAGE with a weight column instead?

No. The AVERAGE function treats all numbers equally and ignores weights. You must use SUMPRODUCT divided by SUM to get a weighted average. There is no shortcut.

What if my weights add up to more than 100?

That is fine. The formula still works. If your weights are 50, 40, and 30 (totaling 120), the division by SUM(weights) scales the result correctly. You will get a valid weighted average.

Can I copy this formula down to calculate weighted averages for multiple groups?

Yes, but you need to use absolute references for the weight column. Write =SUMPRODUCT(A2:A4,B$2:B$4)/SUM(B$2:B$4) with dollar signs before the row numbers in the weights range. This keeps the weights fixed while the values range moves down as you copy the formula.