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 (from executed tests)
All four behave identically in every version of Excel, Google Sheets, and LibreOffice we test. 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).
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.