Weighted Average in Excel: Formula and Examples

To calculate a weighted average in Excel, use =SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7), where column B contains the values and column C contains their weights. The formula multiplies each value by its weight, adds those products, and divides by the total weight.

A weight tells Excel how much each value should count. For example, an order of 50 units should affect the average purchase price more than an order of 10 units. Weights can be quantities, relative importance such as 1, 1, 2, or percentages; they do not have to add up to 1.

The Weighted Average Formula

The general pattern is:

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

Replace values and weights with your cell ranges; those words are placeholders unless you have defined named ranges. The SUM function totals the weights in the denominator. The SUMPRODUCT function supplies the numerator; Microsoft’s SUMPRODUCT documentation explains how it multiplies corresponding entries and adds the results.

Use numeric values and nonnegative weights for the price and score examples below, with at least one positive weight. Keep each value on the same row as its weight. Both ranges must have the same dimensions.

Enter the formulas normally and press Enter. These examples use established functions available in Excel 2016, 2019, 2021, 2024, and Microsoft 365, including current Mac desktop editions; no dynamic-array functions are needed.

Example 1: Average Price Weighted by Quantity

Suppose you buy the same item in six orders at different prices. You want the average amount paid per unit across all orders.

On a sheet named Example, enter the headings in A1:C1 and the following data in rows 2–7. The “Row” column below identifies worksheet rows; do not enter it as an extra column.

In B9, enter:

=SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7)

The result is 11.20 per unit. Format the price and result cells as Currency with two decimal places if these are dollar amounts.

You can check the calculation in two parts:

The total cost is 12×20 + 10×50 + 15×10 + 11×30 + 14×20 + 9×20, or 1,680. Dividing by 150 units gives 11.20. This is the same total-cost-divided-by-total-units method used in Microsoft’s weighted average example.

For comparison, enter =AVERAGE(B2:B7) in B10. It returns approximately 11.83, because it gives each order equal importance regardless of its quantity. The AVERAGE function is appropriate when every numeric observation should count equally.

You can download the weighted average example workbook to change the quantities and compare both results. The examples below use additional sections on its Example sheet.

Example 2: Calculate a Weighted Score

Suppose two tests each count once and a final exam counts twice. All three scores are on the same 0–100 scale.

Put the headings in A16:C16, then enter:

In B21, enter:

=SUMPRODUCT(B17:B19,C17:C19)/SUM(C17:C19)

The weighted score is 88.25:

(90 × 1 + 83 × 1 + 90 × 2) / (1 + 1 + 2)
= 353 / 4
= 88.25

For the equal-weight comparison, enter =AVERAGE(B17:B19) in B22. It returns approximately 87.67. Our guide to using AVERAGE in Excel explains the ordinary mean in more detail.

If assessments have different maximum marks, first convert each to the same scale. A score of 18 out of 20 is 90%, while 80 out of 100 is 80%; averaging the raw marks would mix different units.

Example 3: Use Percentage Weights

The score example can also use weights of 25%, 25%, and 50%. These represent the same relative importance as 1, 1, 2.

Put the headings in A26:C26, then enter:

Enter this formula in B31:

=SUMPRODUCT(B27:B29,C27:C29)/SUM(C27:C29)

It returns 88.25, just as before. Enter =SUM(C27:C29) in B32 and format that cell as Percentage to check that the weights total 100%.

Type each weight with its percent sign, such as 25%. Keep B31 formatted as a number: its inputs are scores such as 90, so its result is 88.25 points out of 100. If you enter scores as percentages instead, such as 90%, format the result as Percentage too.

When weights total exactly 100%, =SUMPRODUCT(B27:B29,C27:C29) alone gives the same answer. Keeping the division makes the formula work with any positive total weight. However, if a grading plan requires weights to total 100%, check that total separately: the formula will rescale an incomplete set of weights, not flag the missing percentage.

Prevent a Divide-by-Zero Error

A weighted average is undefined when the total weight is zero. For the price example, use the IF function to test the denominator before dividing:

=IF(SUM(C2:C7)=0,"No weights",SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7))

This returns No weights when the quantities are all zero or blank. Otherwise, it calculates the weighted average. The check handles the zero denominator; it does not validate the rest of the data.

To try a separate zero-weight test, enter headings in A36:C36, labels Test 1, Test 2, and Final exam in A37:A39, scores 90, 83, 90 in B37:B39, and zeros in C37:C39. In B41, enter:

=IF(SUM(C37:C39)=0,"No weights",SUMPRODUCT(B37:B39,C37:C39)/SUM(C37:C39))

The result should be No weights, rather than a misleading score of zero.

Check Missing Values and Unexpected Results

Decide what a missing score means before calculating. With the comma-separated ranges used here, SUMPRODUCT treats a blank or text score as zero, but its numeric weight still contributes to the denominator. A missing exam score with a positive weight can therefore lower the average without producing an error.

If an assessment is pending, either wait until its score is entered or exclude it from a clearly labeled provisional average by setting its weight to zero. Preserve the original grading weights elsewhere. If your policy counts a missed assessment as zero, enter a numeric 0 deliberately. A real zero score is valid data and should retain its weight.

For other unexpected results, check these points:

• Numbers stored as text: Convert imported prices, scores, and weights to actual numbers. Simply changing the display format may not convert them.
• Mismatched ranges: B2:B7 must pair with C2:C7. Different dimensions cause #VALUE!; an equally sized but shifted range can produce a plausible wrong result.
• Negative weights: These examples assume quantities and importance cannot be negative. Negative weights can move a result outside the input range or cancel the denominator; use them only in a model that explicitly requires them.
• An implausible average: With numeric values and nonnegative weights totaling more than zero, the result must fall between the smallest and largest values that have positive weights.

Once the inputs are clean, the calculation stays the same: multiply each value by its weight, total the products, and divide by the total weight.