Weighted Average in Excel: Formula and Examples

Formulas & functions · Updated November 18, 2026

Excel has no dedicated WEIGHTEDAVERAGE function; use SUMPRODUCT divided by SUM. With values in B2:B4 and their corresponding weights in C2:C4, enter =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4). This multiplies each value by its weight, adds those products, and divides by the total weight.

What a weighted average means

A weighted average gives some observations more influence than others. In a simple average, every observation counts equally. In a weighted average, each observation contributes according to its assigned weight. The calculation is the sum of each value multiplied by its weight, divided by the sum of the weights.

This is useful whenever the items being combined do not represent equal amounts of activity. A course grade might combine homework, quizzes, and exams according to their share of the final grade. A company might combine prices according to the quantities purchased. Averaging the values without accounting for those differences could give a misleading result.

Weights can be entered as percentages, fractions, or ordinary numbers. They do not have to add to 100% because dividing by their sum normalizes the result. However, using percentages that add to 100% makes the weighting easier to inspect and explain.

Worked example with grades

Suppose a grade is made up of an assignment score of 80 worth 50% of the grade, a quiz score of 90 worth 30%, and an exam score of 70 worth 20%. Put the scores in B2:B4 and the weights in C2:C4, entering the weights as 50%, 30%, and 20%. With that layout, use:

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

The weighted points are 80 × 0.50 = 40, 90 × 0.30 = 27, and 70 × 0.20 = 14. Their total is 81, and the weights total 1, so the weighted average is 81. The ordinary average of the three scores would be 80. The difference occurs because the score of 80 carries more weight than either of the other scores.

For this example, the weights sum to 100%, but Excel’s formula also works if they are entered as 50, 30, and 20. The numerator and denominator would both be 100 times larger than with percentage entries, leaving the same result: 81.

How SUMPRODUCT and SUM work

SUMPRODUCT multiplies corresponding items in the ranges and adds the resulting products. In the grade example, it calculates 80 × 50% + 90 × 30% + 70 × 20%. SUM adds the weights, and division by that total ensures the result is on the same scale as the values rather than on the scale of the weighted products.

Both ranges in SUMPRODUCT must line up: the first value must pair with its own weight, the second value with its weight, and so on. The ranges should also have matching dimensions. If one range includes an extra row, Excel may return an error or calculate an unintended result, depending on the exact formula and range shapes.

In Microsoft 365 for Windows, type the formula in a cell and press Enter. Excel for Mac and Excel for the web use the same formula. Some regional settings use semicolons instead of commas between function arguments; if Excel rejects a formula with commas, your regional list separator may be different. The function names and calculation are otherwise the same.

Example: weighted average purchase price

A weighted average also answers practical questions such as “What price did I pay per unit on average?” Suppose quantities are in A2:A4 and their unit prices are in B2:B4: 3 units at $10, 2 at $12, and 5 at $8. With that layout, enter:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)

The total cost is 3 × $10 + 2 × $12 + 5 × $8, or $94. The total quantity is 3 + 2 + 5, or 10 units. The weighted average price is therefore $94 ÷ 10 = $9.40 per unit. The ordinary average of the three listed prices is $10, but that treats each price as if the same number of units were bought at it.

Format the result as currency if it represents a price. The number format changes how the result appears, not how the calculation works. In Excel, select the result cell and choose Home > Number to apply a suitable number format, such as Currency or Percentage.

Choosing appropriate weights

The formula is only as meaningful as the weights. A weight should represent how much each value contributes to the total being summarized, not simply a number that makes a preferred result more prominent. Before calculating, identify what the average is intended to describe and choose weights that match that interpretation.

  • Course grades: use the stated share of the final grade for each assessment category, not the number of individual marks in that category unless that is how the grading scheme is defined.
  • Prices: use units purchased as weights to find the average price per unit.
  • Rates: use the relevant denominators as weights. For example, when combining rates from groups of different sizes, group size or the number of trials may be the appropriate weight, depending on what the rate measures.
  • Survey summaries: use documented sample or population weights when the survey design calls for them. Do not assume that arbitrary weights make a survey representative.

A frequent misconception is that percentages should always be averaged directly. Suppose two groups have different numbers of customers and different satisfaction rates. Taking the simple average of the two rates gives each group equal influence, regardless of size. A weighted average using the number of customers gives a combined rate that reflects the groups’ different sizes. The correct choice depends on the question: an equal-group comparison and a combined overall rate are not the same statistic.

Entering weights and extending the calculation

For a clear worksheet, place the observations and weights in adjacent columns, label each column, and keep one observation-weight pair on each row. Check that each weight is next to the value it belongs to; sorting only one column can silently scramble those pairings and produce a plausible-looking but wrong answer.

If you add records over time, you can extend the ranges in the formula. For example, if the values are in B2:B20 and the weights are in C2:C20, the formula should use those same row limits for both ranges. Excel tables can make expanding data easier to manage because structured references can expand as rows are added, but the basic calculation remains a sum of value-times-weight divided by total weight.

For a small table, SUMPRODUCT is usually the most compact approach. A helper column can be easier to audit when the data is important or when readers need to see each contribution. With values in B2:B4 and corresponding weights in C2:C4, enter this in D2 and fill it down through D4:

=B2*C2

Then, with weighted products in D2:D4 and weights in C2:C4, calculate the total weighted product divided by the total weight with:

=SUM(D2:D4)/SUM(C2:C4)

The helper-column result should match the SUMPRODUCT result. It makes it easier to locate a row that has an unexpected value or weight, although it uses extra worksheet space.

Common errors and checks

One common error is dividing by the wrong total. In the grade example, divide by the sum of the weights, not by the number of grades. Dividing by 3 would not correctly normalize percentage weights. Another mistake is using SUMPRODUCT on values and weights but forgetting the division entirely; that returns the weighted total, not the weighted average.

  • Check the pairings: confirm that each value is multiplied by its intended weight and that both formula ranges cover the same rows.
  • Check the weight total: inspect whether the weights are entered as percentages, decimals, or whole numbers. These conventions can all work, but mixing them can change the result.
  • Check for a zero total: if the sum of the weights is zero, the division has no valid result and Excel returns a division-by-zero error. A set of zero weights does not define an average.
  • Check missing data: decide whether a missing observation should be excluded or treated as zero. Leaving a weight in the denominator while its value is blank can lower the result, so do not let blanks make that decision accidentally.
  • Check for negative weights: ordinary averages generally use nonnegative weights. Negative weights can produce results that are difficult to interpret and should only be used when the model specifically calls for them.

If the result looks unexpectedly small or large, verify units as well. For example, a weight entered as 20 rather than 20% is not automatically wrong if all weights use the same scale, but combining 20 with 0.30 and 0.50 mixes scales. Consistent units and a sensible total make errors much easier to spot.

Alternatives and when to use them

For a straightforward weighted average, SUMPRODUCT divided by SUM is usually preferable to manually multiplying and adding every pair. It is shorter, easier to copy, and less likely to omit a row when the ranges are maintained carefully. A helper column is a reasonable alternative when showing the weighted contribution of each record is important for review.

AVERAGE calculates an unweighted mean, so it is suitable only when every observation should count equally. A pivot table can summarize grouped data, but it does not remove the need to decide how the weighted calculation should be defined. For a complex report with different filters or special rules for missing values, document those rules and test the calculation against a small hand-checked example before relying on the summarized result.

There is no special Excel ribbon command required to calculate a weighted average. The formula can be entered directly in a cell, and the result can then be formatted for its context. This also means the workbook should make the meaning of the weights clear in column headings or notes, so another person can understand what the calculated average represents.

Frequently Asked Questions

Does Excel have a WEIGHTEDAVERAGE function?
No. Excel does not provide a built-in function named WEIGHTEDAVERAGE. The standard method is SUMPRODUCT for the value-weight products divided by SUM for the weights.

Do weights have to add up to 100%?
No. They can add to any positive total because the formula divides by that total. Weights that add to 100% are often easier to interpret, but weights such as 5, 3, and 2 produce the same average as 50%, 30%, and 20%.

Can I use SUMPRODUCT for a weighted average?
Yes. SUMPRODUCT multiplies each value by its corresponding weight and sums those products. Divide that result by the sum of the weights to get the weighted average rather than the weighted total.

Why is my weighted average returning a division-by-zero error?
The denominator, the sum of the weights, is zero. Check for zero weights, negative weights that cancel positive ones, empty or incorrectly referenced cells, and whether the formula points to the intended weight range.

Should I use a weighted average for percentages?
Use one when the percentages represent groups with different relevant denominators and you want a combined rate. Weight each percentage by the appropriate group size or denominator. If the goal is to give every group equal influence, a simple average may instead be appropriate.

Why does my answer differ from AVERAGE?
AVERAGE gives every numeric observation equal influence, while a weighted average gives observations influence according to their weights. The two results match when all observations have equal weights, but otherwise they can differ.

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.