Weighted Average in Excel: Formulas and Examples

Formulas & functions · Updated November 21, 2026

Yes. In Excel, calculate a weighted average by multiplying each value by its weight, adding those products, and dividing by the sum of the weights. With values in B2:B4 and weights in C2:C4, use =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). If the weights add up to 100%, the shorter formula =SUMPRODUCT(B2:B4,C2:C4) returns the same result.

What a weighted average means

A regular average treats every value as equally important. A weighted average gives some values more influence than others. The weight represents how much each value counts, such as a test’s share of a final grade, the number of units sold at a particular price, or a category’s percentage of a total.

For example, a student might receive 88 on work worth 20% of the grade, 92 on work worth 30%, and 75 on a final worth 50%. The result is not the ordinary average of the three scores, because the final has more influence than either of the other assessments. Multiplying each score by its share and adding the results gives 17.6 + 27.6 + 37.5, or 82.7.

Weights can be expressed as percentages, decimals, or counts, depending on the problem. What matters is that the weights match the values and that the calculation accounts for their total. A weighted average is sometimes called a weighted mean.

Set up the data in Excel

Put each value and its corresponding weight on the same row. For a grade calculation, for example, use column B for scores and column C for weights. Keep headings in row 1 and the matching records in rows 2 through 4:

  • B2:B4: scores of 88, 92, and 75.
  • C2:C4: weights of 20%, 30%, and 50%.

Enter weights as percentages by typing values such as 20% and 30%, or enter decimal equivalents such as 0.2 and 0.3. You can also use whole-number weights such as 20, 30, and 50, provided the formula divides by their total. Do not mix representations in the same calculation: 20% and 30 are not equivalent entries.

Alignment is essential. The value in B2 is multiplied by the weight in C2, B3 by C3, and so on. A sorted or pasted weight column that no longer matches its values can produce a valid-looking but incorrect answer.

Calculate the weighted average with SUMPRODUCT

With scores in B2:B4 and their percentage weights in C2:C4, enter this formula in an empty cell:

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

Excel multiplies corresponding cells, adds those products, and divides by the total weight. In this example, the calculation is (88 × 20% + 92 × 30% + 75 × 50%) ÷ 100%, which returns 82.7. The denominator makes the calculation work even if the weights do not total 100.

SUMPRODUCT is useful because it handles the pairwise multiplication and addition in one function. Its arrays or ranges must have compatible dimensions. Here, both ranges contain three rows. If one range runs from row 2 through row 4 and the other from row 2 through row 5, correct the references before relying on the result.

When weights are in D2:D4 and scores are in B2:B4, the same pattern applies:

=SUMPRODUCT(B2:B4,D2:D4)/SUM(D2:D4)

The order of the two SUMPRODUCT ranges does not change the result, but keeping values first and weights second can make the formula easier to read. The denominator must always total the weights used for those values.

When the weights total 100%

If the weights are percentages that add to exactly 100%, their sum is 1 in Excel’s underlying decimal representation. In that case, you can omit the division. With scores in B2:B4 and weights in C2:C4 totaling 100%, use:

=SUMPRODUCT(B2:B4,C2:C4)

For the grade example, this returns 82.7. The longer formula that divides by SUM(C2:C4) also returns 82.7, and it is often safer when weights might later be edited or might not sum precisely to 100%. If someone enters 20, 30, and 50 instead of percentage-formatted values, the shorter formula returns 8,270, not 82.7; the formula with the denominator still returns 82.7.

It is usually better to check why percentages do not total 100% than to assume Excel should silently fix them. Some assignments may intentionally cover only part of a grade, or a weight may be missing. Dividing by the available total normalizes the weights, which may or may not match the intended rules.

A second example: average price per unit

Weighted averages are useful beyond grades. Suppose a shop buys five units at $12 each, two units at $15 each, and three units at $9 each. The average cost per unit should reflect how many units were bought at each price, rather than giving the three prices equal influence.

With unit prices in E2:E4 and quantities in F2:F4, calculate the average price per unit with:

=SUMPRODUCT(E2:E4,F2:F4)/SUM(F2:F4)

The numerator is 12 × 5 + 15 × 2 + 9 × 3, which equals 117. The total quantity is 5 + 2 + 3, or 10, so the result is $11.70 per unit. Format the result cell as currency if you want Excel to display a currency symbol. Formatting changes how the result looks, not the arithmetic.

This illustrates a common use of weights as counts. The total quantity is the denominator because each unit is one observation. For a different question, such as the average of three supplier prices regardless of the number of units, an ordinary average might be more appropriate.

Choose the right weights and denominator

The formula is straightforward; deciding what should count as a weight is often the more important part. A useful check is to state in plain language what one unit of weight represents. It could be one credit hour, one customer, one item, or a defined percentage of a whole.

  • Use percentage weights when categories have specified shares of a total, such as course components worth 20%, 30%, and 50%.
  • Use counts when each counted item represents one observation, such as units sold at each price.
  • Use exposure or duration weights only when the question calls for a result weighted by time or exposure, and confirm that the data measure those quantities consistently.
  • Use an ordinary average when every value should have equal influence, rather than choosing weights merely because they are available.

The denominator should be the sum of the weights for the values included in the numerator. A frequent mistake is to divide by the number of rows instead. That produces a different calculation: it treats the sum of weighted values as though the weights were already normalized, which is only appropriate when their total happens to equal the number required by the setup.

Common errors and data checks

If Excel returns a surprising answer, inspect the inputs before changing the formula. Check that each score is paired with the intended weight, that all included weights are numeric, and that the denominator covers the same records as the numerator. A zero total weight makes the division undefined and causes a division-by-zero error.

  • Weights do not add up as expected: Check whether entries are stored as percentages, decimals, or whole numbers. A cell displaying 20% has the numeric value 0.2.
  • Ranges have different sizes: Make the value and weight ranges cover matching rows. SUMPRODUCT may return an error when array dimensions do not match.
  • Blank or excluded records: Decide whether a missing value should be ignored or treated as zero. Including its weight in the denominator while contributing no value to the numerator lowers the result. Do not let blanks make that decision accidentally.
  • Numbers stored as text: Convert imported numeric-looking text to numbers, or correct the source data. Text can prevent a calculation from behaving as expected.
  • Unexpected display: The cell may be formatted as a date, percentage, or rounded number. Change its number format to inspect the intended result.

For a reusable worksheet, add a check that the weights total what the method expects. For percentage weights, that may be 100%; for quantities, it may be the total number of units. The appropriate check depends on the meaning of the data, not just on a formula convention.

Using the formula in other Excel versions

SUMPRODUCT and SUM are long-standing Excel functions, and the formulas above work in Excel for Microsoft 365 on Windows as well as in current Excel for Mac and Excel for the web. The formulas do not require a special ribbon command: select a cell, type the formula, and press Enter. On Windows, Enter confirms the formula; on Mac, Return normally does the same.

Excel function arguments are shown with commas in this article. Depending on regional settings, Excel may use semicolons as argument separators. If Excel rejects a formula copied from a comma-based example, check the list separator setting for your installation. The formula logic remains the same.

For a list that grows over time, you can make the source range an Excel table so new records are easier to manage. Confirm that the table’s value and weight columns stay paired, and check that the formula includes all intended rows. Whether you use ordinary cell references or a table, the key calculation remains the sum of value-times-weight divided by total weight.

Frequently Asked Questions

Can Excel calculate a weighted average without SUMPRODUCT?
Yes. You can multiply each value by its weight in a helper column, sum that column, and divide by the sum of the weights. SUMPRODUCT is simply a compact way to perform the pairwise multiplications and add them without creating a separate product column.

Do weighted average weights have to add up to 100%?
No. If you divide the weighted sum by the sum of the weights, they can be any positive total. Percentages that do not add to 100% are normalized by that division, so make sure normalization is appropriate for the question. If you omit the division, the weights need to be scaled appropriately, such as totaling 100%.

What is the difference between AVERAGE and a weighted average?
AVERAGE gives each value equal influence. A weighted average gives values different influence according to their weights. Use AVERAGE when each observation counts equally; use the weighted method when the data or the question assigns different importance or frequency to values.

Can weights be zero?
Yes. A zero weight means that value contributes nothing to the weighted result. However, the total of all weights used as the denominator must not be zero. Negative weights are unusual for ordinary averages and should only be used when the underlying method specifically calls for them.

Why does my weighted average look like an ordinary average?
It can happen when all weights are equal, because equal weighting produces the same result as an ordinary average. Also check whether the weights are actually being referenced and whether their values differ as intended. A formula that points to the wrong column can calculate without an obvious error.

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.