AVERAGE vs AVERAGEA (and COUNT vs COUNTA): the A matters
The A-suffix functions treat non-numeric cells as values (text counts as 0, TRUE as 1) instead of skipping them. That's occasionally what you want and more often a silent way to drag an average down with "N/A" strings.
The differences at a glance
| AVERAGE | AVERAGEA | COUNT | COUNTA | |
|---|---|---|---|---|
| Numbers | Included | Included | Counted | Counted |
| Text (including "N/A", "-") | Ignored | Counted as 0 — drags the average down | Ignored | Counted |
| TRUE / FALSE | Ignored | 1 / 0 | Ignored | Counted |
| Empty cells | Ignored | Ignored | Ignored | Ignored |
| Compatibility | Universal | Universal | Universal | Universal — all verified in every version we test |
Which should you use?
- AVERAGE / COUNT — Numeric statistics — placeholder text and flags shouldn't move the result. This is the right default.
- AVERAGEA / COUNTA — COUNTA is the everyday one: counting filled cells of any type (list lengths, response counts). AVERAGEA is rare — only when text genuinely means zero, e.g. scoring sheets where "absent" scores 0.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
All four behave identically in every LibreOffice build and in the Google Sheets run we execute (desktop Excel per Microsoft's docs — we do not run desktop Excel). The subtle cross-check: if AVERAGE and AVERAGEA disagree on a range, that range contains text or booleans — a quick way to detect polluted numeric columns (=COUNTA(rng)-COUNT(rng) counts the non-numeric cells). Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 16 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| Average of the numbers only | =AVERAGE(A2:A100) |
| How many cells are filled (any type) | =COUNTA(A2:A100) |
| Detect non-numeric pollution | =COUNTA(A2:A100)-COUNT(A2:A100) |
Full per-version details on each function page: AVERAGE · AVERAGEA · COUNT · COUNTA.