Weighted Average in Excel: Formula and Examples

Formulas & functions · Updated November 22, 2026

To calculate a weighted average in Excel, multiply each value by its weight, add those products, then divide by the sum of the weights. With values in B2:B4 and their weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). For values 80, 90, and 70 with weights 2, 3, and 1, the result is 83.33.

The Weighted Average Formula

A regular average gives every item the same influence. A weighted average gives items different influence according to their assigned weights. In Excel, SUMPRODUCT multiplies corresponding entries in two ranges and adds the results; SUM totals the weights. Dividing the first total by the second makes the result a properly scaled average.

In general, the calculation is the sum of each value multiplied by its weight, divided by the sum of all weights. The Excel formula assumes that the values and weights line up row by row and that the two ranges contain the same number of cells.

  • Value 1 is multiplied by weight 1.
  • Value 2 is multiplied by weight 2, and so on.
  • The products are added together and divided by the total weight.

For the example in the opening paragraph, the numerator is 80 × 2 + 90 × 3 + 70 × 1, or 500. The weights add up to 6, so 500 ÷ 6 = 83.333…. Displaying two decimal places gives 83.33. The result does not need to be a whole number, even if all the original values are whole numbers.

Why the Weights Matter

A weight represents how much an observation contributes to the final result. It might be a count, such as the number of units sold at each price; a share, such as a course component’s percentage of a final grade; or a measure of importance set by a particular method. The right weights depend on what the data means. Excel can do the arithmetic, but it cannot determine whether a chosen weighting method is appropriate.

Weights do not have to add up to 1 or 100. If all weights are multiplied by the same nonzero number, the weighted average stays the same because both the numerator and denominator change proportionally. For example, weights of 0.2 and 0.8 give the same result as weights of 20 and 80. Percentage weights are therefore convenient, but they are not required.

Zero-weight items contribute nothing to either the weighted total or the total weight. Negative weights are mathematically possible in some specialized calculations, but they are not usually appropriate for ordinary averages of grades, prices, or measurements. Use weights that match the real meaning of the calculation.

Worked Example: Grades and Percentages

Suppose a course grade has two components: a quiz score of 85 worth 40% and an exam score of 92 worth 60%. The weighted result is 89.2, not the simple average of 88.5. The exam matters more because its assigned share is larger.

With the component scores in B2:B3 and their percentage weights in C2:C3, use =SUMPRODUCT(B2:B3,C2:C3)/SUM(C2:C3). The calculation is (85 × 40% + 92 × 60%) ÷ 100%, which equals 89.2. Because the weights total 100%, the denominator normalizes the calculation. If the two weights were entered as 40 and 60 instead, the result would still be 89.2.

For more components, add one row per component and extend both ranges to include every row. Check whether the grading policy specifies category weights or weights individual assignments within each category. Those are different calculations: applying a category percentage to an average of assignments is not always equivalent to weighting every assignment directly.

Set Up and Enter the Formula

A simple worksheet layout keeps the inputs clear and makes the calculation easier to check. Put each value beside its corresponding weight, with headers that identify both columns.

  1. Enter the observations in one column, such as B2:B10, and the matching weights in the next column, such as C2:C10.
  2. Check that each row pairs the correct value with its own weight. A shifted or mismatched row can produce a plausible-looking but incorrect answer.
  3. Choose an empty cell for the result and enter the formula, adjusting both ranges to cover the data.
  4. Press Enter, then apply a suitable number format to the result. For example, use two decimal places for a score or a currency format for a price.

With values in B2:B10 and corresponding weights in C2:C10, enter =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10). In Microsoft 365 for Windows, this is an ordinary worksheet formula; it does not require Ctrl+Shift+Enter. The same formula works in Excel for Mac and Excel for the web, though ribbon layouts and some keyboard shortcuts can differ. If the data is in an Excel Table, structured references can make formulas easier to read, but ordinary cell ranges are often simplest for a first calculation.

Weighted Average for Prices and Quantities

A common practical use is finding the average price paid when different quantities were purchased at different prices. A simple average of the prices would give a small purchase the same influence as a large one. Weighting each price by its quantity instead answers the question, What was the average cost per unit across all the units purchased?

With unit prices in B2:B5 and quantities purchased at those prices in C2:C5, use =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). For example, if 10 units cost $5 each and 30 units cost $7 each, the total is $260 for 40 units, so the weighted average unit price is $6.50. This is a calculation of an average from the supplied prices and quantities, not financial or tax advice; verify the inputs and any relevant terms with the appropriate source or a qualified professional.

The same approach works for average delivery time weighted by shipment count, average test results weighted by the number of observations, or average ratings weighted by the number of ratings. In each case, confirm that the weight represents the number or importance that should affect the average.

Weighted Average for One Category

Sometimes the data contains several groups, but the calculation should include only one, such as one region or department. A weighted average over filtered-looking rows does not automatically mean that hidden rows are excluded: ordinary formulas generally still use their referenced cells. You can instead apply a condition inside the calculation.

Assume the category names are in A2:A10, values are in B2:B10, weights are in C2:C10, and the category to include is entered in E1. Use =IFERROR(SUMPRODUCT((A2:A10=E1)*B2:B10*C2:C10)/SUMIFS(C2:C10,A2:A10,E1),NA()). This calculates the weighted average for rows matching E1. If there are no matching rows, or their weights total zero, it returns the #N/A error rather than a misleading numeric result. Change E1 to another category to recalculate. This formula assumes the value and weight cells contain appropriate numeric data.

Common Errors and How to Check Them

Most incorrect results come from mismatched data or an unexpected denominator rather than from the basic formula. Before relying on the output, check the inputs and compare the calculation with a small manual example.

  • Different range sizes: The value and weight ranges should cover corresponding rows and have the same number of cells. SUMPRODUCT can return an error when its array dimensions do not match.
  • Weights sum to zero: The division is undefined when the denominator is zero, including when every weight is zero. Decide how that case should be handled instead of interpreting an error as an average.
  • Numbers stored as text: Text entries may not participate in arithmetic as intended. Check imported data, symbols, and cells that look numeric but are left-aligned or have a warning indicator.
  • Blank or missing observations: Decide whether a blank means zero, unknown, or not applicable. Treating a missing value as zero can change the result substantially.
  • Wrong weight column: Confirm that weights are counts or shares for the values beside them, not another measure with similar-looking numbers.
  • Rounding too early: Keep full precision in the inputs and format the displayed result. Rounding each product or intermediate result can introduce avoidable differences.

If a workbook should show a blank or a message when weights total zero, use an explicit test rather than allowing a division error. With values in B2:B10 and weights in C2:C10, this formula returns #N/A when the weight total is zero and otherwise calculates the weighted average: =IF(SUM(C2:C10)=0,NA(),SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)). Using NA() makes the exceptional case visible to other calculations and charts. Choose a different handling method only if it reflects what the result should mean.

Weighted Average Versus AVERAGE

Excel’s AVERAGE function calculates the arithmetic mean of numeric entries, giving each included number equal influence. It is appropriate when observations should count equally. A weighted average is appropriate when some observations represent more units, have larger shares, or otherwise carry different justified influence.

For instance, the average of prices $5 and $7 is $6 if each price represents the same quantity. If the first price applies to 10 units and the second to 30, the weighted average is $6.50. Neither answer is inherently wrong; they answer different questions. Use the equal-weight average for the average of the two listed prices, and the quantity-weighted result for the average cost per unit across the 40 units.

Do not confuse the weighted average with a median or with a percentage change. Those statistics answer different questions. If you need the middle observation, consider a median; if you need change over time, define the relevant starting point and comparison method rather than assuming an average is the right measure.

Frequently Asked Questions

Do weighted average weights have to add up to 100%?
No. The formula divides by the sum of the weights, so weights of 20 and 80 produce the same weighted average as 20% and 80%, provided the weights are scaled consistently and their total is not zero.

Can I use percentages as weights in Excel?
Yes. Enter percentages as percentages, such as 40% and 60%, and use them as the weight range. The formula divides by their total, so the weights do not have to add up to exactly 100% for the calculation to normalize them.

What if all the weights are zero?
A weighted average is undefined because the formula would divide by zero. Check the source data and decide whether to show an error, a message, or no result; do not substitute an arbitrary value without a reason.

Does sorting the data change the weighted average?
Sorting does not change the result if each value stays paired with its original weight. If values and weights are sorted separately, their pairings break and the result may be wrong.

Can I calculate a weighted average from a filtered list?
Filtering rows on screen does not necessarily exclude them from a formula that references the full range. Use a condition-based formula, such as the category example above, when the calculation must include only matching records.

Why is my result different from the simple average?
The two calculations give observations different influence. A weighted average shifts toward values with larger weights; if every weight is equal, it matches the ordinary average, subject to the same included data.

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.