AVERAGEA in Google Sheets: Count Text as Zero in an Average
Use Sheets AVERAGEA with explicit text controls, weighted groups and denominator checks, while separating missing data from genuine zero scores.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: AVERAGEA computes a mean while counting text as zero. With numeric 6, 12 and the text Pending, =AVERAGEA(A2:A4) has expected result 6, because the calculation is (6+12+0)/3. This is useful only when treating that label as a zero observation is intentional.
Availability and version history
- Introduced: first service launch date is not established by the official history reviewed; no numbered desktop introduction is assigned.
- Availability: documented in the rolling Google Sheets service. Examples target ordinary web-spreadsheet cells; mobile clients and imported file modes are not separately certified.
- Checked: October 14, 2026; official AVERAGEA reference. Documentation and independently calculated examples, without native Sheets execution.
Syntax and participation
=AVERAGEA(value1, [value2, ...])
Provide one value or range, followed by optional additional values or ranges. Google explicitly says text participates as zero and directs users to AVERAGE when text should be ignored. Its reference also says Sheets accepts an arbitrary number of arguments despite an older 30-argument specification; this is not a tested performance guarantee.
The examples use English names and comma separators. Adapt separators to the sheet locale. Do not copy a desktop spreadsheet’s complete Boolean, blank or error rules into Sheets from the shared function name.
Example 1: inspect the denominator
Enter this data in A1:B4:
| Row | A · Result | B · Meaning |
|---|---|---|
| 1 | Result | Meaning |
| 2 | 6 | Measured score |
| 3 | 12 | Measured score |
| 4 | Pending | Unfinished assessment |
Put these formulas outside the table:
=AVERAGEA(A2:A4)
=AVERAGE(A2:A4)
Expected results are 6 and 9, respectively. The first calculation has three participants; AVERAGE uses the two numeric scores. Neither formula can decide whether an unfinished assessment ought to reduce the reported score.
If you replace Pending with numeric zero, both functions have expected result 6. A zero measurement and a pending label can produce the same AVERAGEA output even though they describe different situations.
Example 2: include more than one range
In D2:D3 enter numeric 18 and text Pending. Then calculate:
=AVERAGEA(A2:A4,D2:D3)
Expected result: 7.2, from the combined total 36 divided by five participants. The second range contributes 18 and zero. The formula combines observations; it does not first average each range and then average those two results.
For comparison, the individual range means are 6 and 9. Their unweighted average is 7.5, which gives the two-record range the same influence as the three-record range. Reconstructing the overall mean requires the actual participant counts:
=(6*3+9*2)/5
That independently gives 7.2 again. These counts are explicit for the supplied filled cells, not inferred from an untested blank-classification rule.
Advanced example: measure a reporting policy
Enter numeric 80, 60 and the text Absent in F2:F4.
=AVERAGEA(F2:F4)
=AVERAGE(F2:F4)
The expected values are approximately 46.666666667 and 70. The gap comes entirely from whether the absent record contributes a zero participant. If policy says the record should be excluded, AVERAGEA implements the wrong policy despite using valid syntax.
Replace Absent with numeric 70 after the assessment is completed. Both functions now produce 70. Record the policy separately from the formula so later editors know why the earlier report was lower.
Advanced check: overlapping references repeat an observation
Return to A2:A4 containing 6, 12 and Pending:
=AVERAGEA(A2:A4,A2)
Expected result: 6, from (6+12+0+6)/4. That happens to match the original mean; it does not prove there was no overlap. Repeating A3 instead gives =AVERAGEA(A2:A4,A3), with expected result 7.5.
A matching total or average is a weak duplication check. Inspect the references themselves when combining subtables, especially if their boundaries meet at a shared row.
Limits, blanks and error handling
The selected Google page establishes the text-as-zero rule but does not provide a complete Boolean, physical-blank, formula-empty or error-code matrix. These examples deliberately use numeric values and ordinary filled labels. Do not label every visually empty cell as a counted zero, or every logical flag as one, without separate Sheets evidence.
Use COUNT and COUNTA as separately researched diagnostics; neither should be assumed to reproduce every AVERAGEA participant rule. Converting a text score to a real number changes its meaning here, whereas merely applying a number format does not establish conversion.
References and verification
Inputs are fictional. Approximate answers are independent mathematical expectations rounded to nine places, not captured native results. Complete type/error matrices, extreme-magnitude accuracy and cross-client behavior remain untested.