Calculate a Weighted Average in Excel: Formula and Examples

Formulas & functions · Updated November 21, 2026

To calculate a weighted average in Excel, multiply each value by its weight, add those products, and divide by the sum of the weights. With values in B2:B4 and their weights in C2:C4, use =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). This works whether weights are entered as percentages or as numbers such as 50, 30, and 20.

What a weighted average means

A regular average gives every observation the same influence. A weighted average gives some observations more influence because they matter more to the question being answered. The weight might represent a share of a grade, a quantity sold, a portion of a portfolio, or the number of people represented by a group average.

For example, if a course grade is based on homework worth 20%, a midterm worth 30%, and a final worth 50%, the final should affect the course result more than homework. Averaging the three scores equally would ignore those stated proportions. A weighted average accounts for them by multiplying each score by its corresponding weight.

In general, the calculation is the sum of each value multiplied by its weight, divided by the sum of all weights. Dividing by the weight total is important: it makes the result a proper average even when the weights are not entered as percentages that add to exactly 100. If the weights are already proportions totaling 1, the denominator is 1 and does not change the result.

Set up the data in Excel

Put each value and its matching weight on the same row. A clear layout makes it easier to check that you have paired the right observations and weights. For instance, enter the score in column B and its course percentage in column C:

  • A1: Assessment; B1: Score; C1: Weight.
  • Row 2: Homework; score 92; weight 20%.
  • Row 3: Midterm; score 85; weight 30%.
  • Row 4: Final; score 78; weight 50%.

Enter percentage weights as percentages, such as 20%, rather than as the whole number 20 if you want the sheet to display them as proportions. Excel stores 20% as 0.2 for calculation purposes. You can format the weight cells as Percentage using Home > Number, or enter a percent sign directly when typing each value. The formula also works with whole-number weights, provided the values are on the same scale and the formula divides by their total.

Keep the value range and weight range the same size and in the same order. A weight on row 3 applies to the value on row 3; the formula cannot know if the rows have been mismatched.

Work through a grade example

With scores in B2:B4 and weights in C2:C4 as shown above, enter the following in an empty cell, such as B6:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

The result is 82.1. The arithmetic is 92 × 0.20 = 18.4, 85 × 0.30 = 25.5, and 78 × 0.50 = 39. Add those weighted scores to get 82.9, then divide by the weights’ total of 1.00, giving 82.9. The correct result is therefore 82.9, not 82.1.

Because the calculation is easy to misread, check it step by step: 18.4 + 25.5 + 39 = 82.9, and 20% + 30% + 50% = 100%. Dividing 82.9 by 1 returns 82.9. The formula result is 82.9. If your spreadsheet shows a different result, check the entered data, the ranges, and whether the weights are numeric percentages rather than text.

How SUMPRODUCT and SUM work together

SUMPRODUCT multiplies corresponding cells and adds the products. In the example, it pairs B2 with C2, B3 with C3, and B4 with C4, then totals those three products. SUM adds the weights in C2:C4. Dividing the first total by the second normalizes the result to the scale of the original values.

Using SUMPRODUCT is convenient because it performs the row-by-row multiplication without requiring a separate column of intermediate calculations. For a more auditable worksheet, you can create a fourth column called Weighted score, calculate each value’s contribution in its own row, and total that column. This makes it easier to inspect individual contributions, though it adds more cells to maintain.

The two ranges supplied to SUMPRODUCT must have matching dimensions. A range of three scores paired with four weights is a data-layout error, not a meaningful calculation. When you add new records, expand both ranges together. Excel for Microsoft 365 on Windows uses the formula as written; it also works in Excel for Mac and Excel for the web. Depending on regional settings, Excel may display semicolons instead of commas as formula separators.

Choose weights that match the question

The arithmetic is only as meaningful as the weights. Weights should represent the relative importance or quantity relevant to the calculation, not merely values that happen to be available. A grade’s published assessment percentages are appropriate weights for a course mark. The number of units sold is appropriate when calculating an average selling price per unit. Neither set of weights would automatically be appropriate for a different question.

  • Percent weights: Use them when each category is a share of a total, such as a project worth 40% of a grade.
  • Frequency or count weights: Use counts when a group-level average represents a known number of observations. A group of 100 people should generally have more influence than a group of 5 when combining their averages.
  • Quantity weights: Use quantities when the desired result is an average per item, such as average price per unit across products sold in different amounts.

Weights do not have to total 100 before you use the formula, because the denominator divides by their total. But they must be nonnegative for the usual interpretation as relative shares, and they should be based on a defensible method. If the weights total 80 rather than 100, Excel still calculates the weighted average; that fact alone does not mean the result is wrong.

Common mistakes and how to avoid them

  • Taking an ordinary average instead: AVERAGE gives every listed value equal influence. Use it only when that is what the data calls for.
  • Forgetting to divide by total weight: SUMPRODUCT by itself gives the weighted sum. It is an average only when the weights total exactly 1, or 100%.
  • Mixing percentage and whole-number scales: Entering 20% as 20 while entering other weights as 30% creates a very different calculation. Use one consistent representation.
  • Pairing the wrong rows: Sort the value and weight columns together. Sorting one column independently can attach weights to the wrong values.
  • Using unequal or shifted ranges: Check that both ranges start and end on the same rows and contain the same number of entries.
  • Rounding too early: Keep full precision in the underlying values and use number formatting to show fewer decimal places. Rounding each product before summing can slightly change the final answer.

Also check that cells intended to contain numbers are numeric. A number imported as text may not behave as expected, and a blank or missing value can conceal incomplete data. Do not silently assign a missing value a zero unless zero is genuinely the right value; doing so can pull the average down. Decide whether the observation should be excluded, corrected, or treated according to a documented rule.

Check the result and make the worksheet clear

A useful reasonableness check is that a weighted average with positive weights should fall between the smallest and largest included values. If the included scores range from 78 to 92, a result such as 105 signals a likely data or formula problem. If all values are identical, the weighted average should equal that value regardless of the weights.

Use a label such as Weighted average beside the result, and format it to a suitable number of decimal places. For a grade report, one decimal place may be readable; for operational data, the appropriate precision depends on how the result will be used. Formatting changes the display, not the stored calculation. Keep the source weights visible and document what they mean, especially if someone else will review or update the workbook.

If you want to compare the weighted result with a simple average, calculate the two separately and label them clearly. A difference between them is not necessarily an error: it can show that higher-weighted observations tend to be higher or lower than the rest. The right measure depends on the question, so do not select one just because its result appears more favorable.

Frequently Asked Questions

How do I calculate a weighted average in Excel with percentages?
Place each value beside its percentage weight and use SUMPRODUCT for the weighted products divided by SUM for the weights. Percentage cells are stored as decimal proportions, so weights totaling 100% produce a denominator of 1.

Do the weights have to add up to 100%?
No. When the formula divides the weighted sum by the sum of the weights, the weights can total any positive amount. They should still be on a consistent scale and represent the intended relative importance.

What is the difference between SUMPRODUCT and AVERAGE?
AVERAGE treats each value equally. SUMPRODUCT can multiply each value by its corresponding weight; dividing that result by the total weight produces a weighted average.

Can I use the formula if my weights are 50, 30, and 20?
Yes. The formula divides by the weights’ total of 100, so using 50, 30, and 20 gives the same weighted average as using 50%, 30%, and 20%, as long as all weights use the same scale.

Why is my weighted average different from the expected result?
Check that the value and weight ranges have matching sizes, the rows are correctly paired, and the weights use a consistent numeric scale. Verify the weighted products and their total separately, and check for missing or text-formatted values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.