AVERAGE.WEIGHTED in Google Sheets: Weights, Examples and Errors

=AVERAGE.WEIGHTED(values, weights, [additional_values, additional_weights, ...])

• values supplies the numbers to average, as values or a cell range.
• weights supplies the corresponding importance or frequency of each number. Range dimensions must match the values: the same rows and columns, not merely the same number of cells.
• Extra inputs come in value/weight pairs. Each extra value argument needs its own weight argument.
• Zero weights are permitted. Negative weights are not; at least one weight must be greater than zero.

These are the constraints in Google’s AVERAGE.WEIGHTED reference. Square brackets show optional pairs; do not type them. The examples use English function names, comma separators and decimal points.

Enter the following values in A1:C4. Leave E2:E6 available for results.

In E2 enter:

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

Expected result: 14.5. The purchases cost 120, 90 and 225, totaling 435 for 30 units. Dividing 435 by 30 gives the average price per unit.

Compare E3:

=AVERAGE(B2:B4)

Expected result: 15. That answers a different question: the average of the three listed prices, treating every delivery equally regardless of quantity.

For an arithmetic cross-check of this clean numeric dataset, enter in E4:

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

Expected result: 14.5. This equivalence checks the mathematical calculation; it does not establish identical handling of errors, text or missing data in every formula.

A course uses scores 80, 90 and 70, with weights 20%, 30% and 50%:

=AVERAGE.WEIGHTED(80,20%,90,30%,70,50%)

Expected result: 78, calculated as 16 + 27 + 35. Entering weights 20, 30 and 50 instead produces the same mathematical weighted mean because numerator and denominator both grow by 100.

Likewise, changing the purchase weights from 10, 5, 15 to 0.10, 0.05, 0.15 leaves the result 14.5. These weights sum to 0.30; normalization is still part of the weighted-average calculation. If a report requires allocation percentages totaling 100%, validate that business rule separately. A plausible average does not prove the allocation is complete.

Change C3 from 5 to 0, keeping the other original values. The expected weighted mean becomes 13.8: (12×10 + 18×0 + 15×15) / 25. The zero-weight price contributes nothing to either the weighted total or total weight.

Restore C3 to 5. To include a separate purchase of 10 units at price 20 without extending the table, use:

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

Expected result: 15.875, from (435 + 200) / (30 + 10). The last two arguments are price then units; reversing them changes the model.

Suppose one non-overlapping group contains nine observations averaging 10, and another contains one observation averaging 20. With group means in G2:G3 and observation counts in H2:H3:

=AVERAGE.WEIGHTED(G2:G3,H2:H3)

Expected result: 11. An ordinary mean of the group averages would give 15, overrepresenting the one-observation group. The correct weights are the counts actually used in each mean, measured under the same inclusion rule. Do not use total row counts if some source observations were excluded.

The groups must not overlap. This formula cannot detect duplicated people, transactions or measurements hidden inside aggregate summaries. Nor can it reconstruct a combined variance from group means and counts alone.

• Mismatched ranges: B2:B4 and C2:C5 have different heights. Correct the boundaries rather than deleting an arbitrary weight. A three-row column also does not match a three-column row.
• All weights zero: There is no weighted mean because its denominator is zero. Decide whether the report should show a missing result or a message; zero is not a substitute average.
• Negative weight: Recheck the data model. A return, correction or negative ledger entry may need net-total accounting rather than this nonnegative-weight function.
• Wrong result without an error: Verify that prices and weights stayed paired after sorting. Sort the whole table together.
• Imported text or blanks: Convert intentional numeric fields to numbers and make the missing-value policy explicit. The cited function page does not establish a complete blank/text/coercion or error-code matrix; this guide does not assume one.

First test the supplied numeric table. Then add real data in a small block. Check units as well as numbers: mixing kilograms and individual pieces as weights produces a mathematically valid but meaningless average.

AVERAGE.WEIGHTED is documented in the current rolling Google Sheets service. The reviewed sources do not establish its original launch date, an introduction version, or a last functioning numbered release. This guide concerns ordinary worksheet formulas, not a separately specified database connector.

When transferring the model to another spreadsheet application, verify that application’s function name and validation rules. For clean numeric inputs, weighted sum divided by total weight provides a useful mathematical comparison; it is not a blanket promise of product compatibility.

Evidence status: NOT_TESTED_IN_GOOGLE_SHEETS. Google’s individual reference was inspected on October 17, 2026. All example answers were independently calculated; no Google Sheets session, native execution or screenshot was used.

Related published guides: SUMPRODUCT, SUM, AVERAGE and AVERAGEIFS.