AVERAGE.WEIGHTED calculates a mean where some values contribute more than others. Give Sheets one values range and a matching weights range.
For grades in B2:B6 and course weights in C2:C6, use =AVERAGE.WEIGHTED(B2:B6,C2:C6). The tested result was 87.7.
Calculate a Weighted Average in Google Sheets
| Item | Grade | Weight |
|---|---|---|
| Homework | 95 | 25% |
| Participation | 90 | 10% |
| Midterm | 85 | 15% |
| Projects | 88 | 20% |
| Final | 82 | 30% |
=AVERAGE.WEIGHTED(B2:B6,C2:C6)The formula returned 87.7. Higher-weight items affected the result more than lower-weight items.
A plain AVERAGE would give every grade equal influence, regardless of its course weight.
AVERAGE.WEIGHTED Function Syntax
=AVERAGE.WEIGHTED(values,weights,[additional_values],[additional_weights])- values contains the numbers to average.
- weights contains the influence assigned to each corresponding value.
- additional_values and additional_weights add another matched pair.
Each values range must have the same rows and columns as its weights range.
How the Weighted Average Formula Works
Sheets multiplies every value by its weight, adds those products, then divides by the sum of the weights.
=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)This SUMPRODUCT formula expresses the same calculation and can help when a workbook must also work in Excel.
Use Weights That Do Not Add to 100%
Weights can be counts, quantities, or other non-negative numbers. They do not need to total 1 or 100.
The test used values 8 and 5 with weights 5 and 1:
=AVERAGE.WEIGHTED(A9:A10,B9:B10)The result was 7.5 because the value 8 carried five times the influence of the value 5.
A zero weight is allowed and contributes nothing. In the test, values 100 and 50 with weights 0 and 1 returned 50.
Weight Ratings by Response Count
Response counts are useful weights when product ratings have different sample sizes.
Enter these averages and response counts in G1:H4:
| Average rating | Responses |
|---|---|
| 4.8 | 12 |
| 4.2 | 40 |
| 4.6 | 8 |
=AVERAGE.WEIGHTED(G2:G4,H2:H4)Products with more responses contribute more to the combined rating.
Combine Additional Value and Weight Ranges
AVERAGE.WEIGHTED accepts another values-and-weights pair after the first pair:
For example, enter East prices and units in J2:K3 as 12/4 and 14/2. Enter West prices and units in L2:M3 as 11/3 and 15/5.
=AVERAGE.WEIGHTED(J2:J3,K2:K3,L2:L3,M2:M3)This can combine East prices and units with West prices and units without stacking the source ranges first.
Fix Invalid Weights and Mismatched Ranges
The error message identified the negative weight directly: “Cannot calculate a weighted average with a negative weight. (Found -1).”
A three-row values range paired with a two-row weights range also returned #VALUE!. Use ranges with matching dimensions.
See Google’s AVERAGE.WEIGHTED reference for the argument rules and official grade example.
Other Google Sheets articles you may also like