Google Sheets AVERAGE.WEIGHTED Function: Syntax, Examples & Tips

What Is AVERAGE.WEIGHTED?

AVERAGE.WEIGHTED calculates a mean in which some values count more than others. Each value is multiplied by its weight, the products are added up, and the total is divided by the sum of the weights. This is the right calculation whenever items differ in importance or size, such as course grades with different credit values or prices across orders of different quantities.

A plain AVERAGE treats every value equally, which can give a misleading answer when, say, one order is for 1,000 units and another is for 2.

How AVERAGE.WEIGHTED Works

The calculation is:

weighted average = (value₁ × weight₁ + value₂ × weight₂ + …) ÷ (weight₁ + weight₂ + …)

The values and weights ranges must be the same size, and they are paired by position: the first value goes with the first weight, and so on. Weights do not need to add up to 1 or 100. Google Sheets divides by their total, so weights of 2, 3, and 5 behave exactly like 20%, 30%, and 50%.

Basic Examples

Weighted grade

Scores in B2:B4 (85, 92, 78) and weights in C2:C4 (20%, 30%, 50%):

=AVERAGE.WEIGHTED(B2:B4, C2:C4)

Returns 83.6: (85×0.2 + 92×0.3 + 78×0.5) ÷ 1.

Average price weighted by quantity

=AVERAGE.WEIGHTED(C2:C100, D2:D100)

With unit prices in C and quantities in D, this returns the true average price paid per unit across all orders.

Typed values and weights

=AVERAGE.WEIGHTED({10, 20, 30}, {1, 1, 2})

Returns 22.5, because the 30 counts twice.

Combining two blocks of data

=AVERAGE.WEIGHTED(B2:B10, C2:C10, F2:F10, G2:G10)

Treats both value/weight pairs as one data set.

The Equivalent SUMPRODUCT Formula

Before AVERAGE.WEIGHTED existed, people wrote:

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

Both formulas return the same answer. AVERAGE.WEIGHTED is shorter and states the intent more clearly. The SUMPRODUCT version is still useful when you need to add conditions inside the calculation.

Common Use Cases

• Grading: assignments, exams, and projects with different weights
• GPA: grade points weighted by course credits
• Pricing: average unit price weighted by units sold
• Finance: average return of a portfolio weighted by position size
• Surveys: responses weighted to match a population

Common Errors

• Mismatched sizes: If values and weights have different dimensions, the formula returns an error. Check that both ranges start and end on the same rows.
• Weights summing to zero: The formula divides by the total weight, so zero total weight produces a division error.
• Negative weights: Weights cannot be negative, though they can be zero, and at least one weight must be positive.
• Text in the ranges: Make sure both ranges contain only numbers; clean imported data first.

Tips and Best Practices

• Store weights as percentages or as raw counts, whichever is easier. The result is the same because the function normalises by their total.
• Show the total weight next to the result with SUM so readers can see what the average is based on.
• Round the output with ROUND for reports.
• To weight only some rows, filter both ranges with the same FILTER condition, or use SUMPRODUCT with a condition.

Related Functions

• AVERAGE: equal-weight mean
• AVERAGEIF: mean of values meeting a condition
• SUM: total of the weights
• ROUND: format the final average