The basic formula for a weighted average

A weighted average in Excel uses the SUMPRODUCT function to multiply each value by its weight, then divides by the sum of all weights. The formula is:

=SUMPRODUCT(values, weights) / SUM(weights)

If you have test scores in cells B2:B5 and their weights in C2:C5, you would write =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). Excel multiplies each score by its weight, adds those products together, then divides by the total weight to give you the final average.

This works because a weighted average is not the same as a simple average. A simple average treats every number equally. A weighted average gives some numbers more importance than others — which is why you need the weights in the first place.

Key Takeaways

  • Use SUMPRODUCT to multiply each value by its weight, then divide the result by SUM of the weights.
  • Weights do not have to add up to 100 or 1 — Excel handles any total weight automatically.
  • If your data includes text or blank cells, SUMPRODUCT will skip them without breaking the formula.
  • You can use the same weighted average formula whether your weights are percentages, point values, or any other number system.

Setting up your data in the spreadsheet

Arrange your values in one column and their corresponding weights in another column next to it. For example, if you are calculating a grade based on assignments, tests, and participation, put the scores in column B and the weights in column C. Make sure each weight is on the same row as its value.

Your weights can be percentages (like 30, 40, 30), decimals (like 0.3, 0.4, 0.3), or whole numbers (like 3, 4, 3). The formula works the same way regardless. If you use percentages that add up to 100, the result will be out of 100. If you use decimals that add up to 1, the result will be between 0 and 1 (or whatever your highest value is).

Leave the first row for headers if you want to keep your spreadsheet organized. Start your actual data in row 2. This makes it easier to see what each column represents and prevents Excel from trying to include your headers in the calculation.

Writing the SUMPRODUCT formula step by step

Click on the cell where you want the weighted average to appear. Type the equals sign to start a formula. Then type SUMPRODUCT(, followed by the range of values, a comma, and the range of weights. Close the parenthesis and divide by the sum of weights.

If your values are in B2:B10 and weights are in C2:C10, the complete formula looks like this: =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). Press Enter and Excel calculates the result immediately.

The ranges must be the same size — if you have 9 values, you must have 9 weights. If the ranges are different lengths, Excel will return an error. Double-check that both ranges start and end on the same rows.

Real example: calculating a course grade

Suppose your course grade is based on three components: assignments (40% weight), midterm (30% weight), and final exam (30% weight). Your scores are 85 on assignments, 78 on the midterm, and 82 on the final exam.

Put 85, 78, and 82 in cells B2, B3, and B4. Put 40, 30, and 30 in cells C2, C3, and C4. In cell B5, write =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). Excel calculates (85×40 + 78×30 + 82×30) ÷ 100 = 8,110 ÷ 100 = 81.1. Your weighted average is 81.1.

If you had used a simple average instead, you would get (85 + 78 + 82) ÷ 3 = 81.67, which is different because it treats all three scores equally instead of giving more weight to the assignments.

Checking your formula for common mistakes

The most common error is using the wrong cell ranges. Make sure the values range and weights range are the same size and aligned correctly. If you have 5 values, you must have exactly 5 weights on the same rows.

Another mistake is forgetting to divide by the sum of weights. If you write only =SUMPRODUCT(B2:B5,C2:C5) without the division part, you will get a number that is too large because it has not been scaled down by the total weight.

If your formula returns #VALUE! error, check whether any cells contain text or are blank. SUMPRODUCT skips text and blanks automatically, but if an entire row is empty, it might cause confusion. Make sure every value has a corresponding weight and vice versa.

Using weighted averages with different weight systems

Your weights do not have to be percentages. You can use any number system as long as the weights represent relative importance. If you want to average three test scores where the first test counts once, the second counts twice, and the third counts three times, use weights of 1, 2, and 3. The formula stays the same.

You can also use decimal weights. If your weights are 0.4, 0.3, and 0.3 (which add up to 1), the formula works identically. The result will be on the same scale as your original values — if your scores are out of 100, the weighted average will also be out of 100.

Some people use weights that do not add up to 100 or 1. For example, you might weight assignments as 4, quizzes as 2, and the final as 3. The formula =SUMPRODUCT(values,weights)/SUM(weights) handles this automatically by dividing by the total weight (9 in this case).

Frequently Asked Questions

What if my weights do not add up to 100?

The formula still works correctly. SUMPRODUCT multiplies each value by its weight and adds them up, then divides by the sum of all weights. Whether your weights total 100, 1, or any other number, the division by SUM(weights) scales the result appropriately.

Can I use AVERAGE.WEIGHTED instead of SUMPRODUCT?

Excel does not have a built-in AVERAGE.WEIGHTED function. SUMPRODUCT is the standard way to calculate weighted averages in Excel. Some versions of Excel or other spreadsheet programs may have different functions, but SUMPRODUCT works in all versions.

What happens if a cell in my weights column is blank?

SUMPRODUCT treats blank cells as zero, so that row contributes nothing to the weighted average. If you have a blank weight, the corresponding value is ignored. Make sure every value has a weight assigned, even if the weight is zero.

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

Yes. After you write the formula once, click the cell and drag the fill handle (small square at the bottom right) down to copy it to other rows. Excel automatically adjusts the cell references. Make sure your data is organized so each row has its own set of values and weights.

How do I know if my weighted average is correct?

Check by hand: multiply each value by its weight, add all those products together, then divide by the sum of weights. If your manual calculation matches the Excel result, the formula is correct. Also verify that your weights are assigned to the right values and that you are using the correct cell ranges.