Both compute a mean, but they disagree about what counts. AVERAGE uses only numeric cells and ignores text and logical values. AVERAGEA counts text as 0 and TRUE as 1 (FALSE as 0), so non-numeric entries drag the mean down. The "A" again means "all". Below, the same mixed range is averaged by both, live.
AVERAGE uses only numbers (20 on the mixed range: the mean of 10, 20, 30); AVERAGEA counts text as 0 and TRUE as 1, so the same range gives 61/6 = 10.17. Use AVERAGE for a true numeric mean, AVERAGEA when non-numeric entries should count as zero.
What each function includes in the mean.
| Capability | AVERAGE | AVERAGEA | Why it matters |
|---|---|---|---|
| Averages numeric cells | ✓ Numbers only | ✓ Numbers included | Both include 10, 20, 30. |
| Text in a range | ✗ Ignored | ✓ Counted as 0 | AVERAGEA adds a 0 and grows the denominator. |
| Logical TRUE in a range | ✗ Ignored | ✓ Counted as 1 | TRUE in B7 adds 1 to AVERAGEA only. |
| Empty string "" in a range | ✗ Ignored | ✓ Counted as 0 | "" lowers the AVERAGEA mean. |
| Truly blank cells | ✓ Skipped | ✓ Skipped | Neither counts a genuinely empty cell. |
| No numbers in selection | ✓ #DIV/0! | ✓ Mean of coerced 0/1 | AVERAGE errors; AVERAGEA can return 0. |
| Literal text argument | ✓ #VALUE! | ✓ #VALUE! | In HyperFormula a direct text arg errors in both; range text differs. |
number1… (numeric) vs value1… (text=0, TRUE=1).
One editable mixed column, two means. Pick a scenario or type your own — watch text and TRUE drag the AVERAGEA mean down.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=AVERAGE(B2:B7)=AVERAGEA(B2:B7)=AVERAGE(B2,B6)=AVERAGEA(B2,B6)=AVERAGE(B3)=AVERAGEA(B3)=AVERAGE(B7)=AVERAGEA(B7)=AVERAGE(B5)=AVERAGEA(B5)=AVERAGE(B2)=AVERAGEA(B2)=AVERAGE(B2:B5)=AVERAGEA(B2:B5)Engine cost averaging a mixed numeric/text/logical column (relative only, not Microsoft Excel).
| Table size | AVERAGE | AVERAGEA |
|---|---|---|
| 1,000 rows × 200 lookups | 67.98 ms | 60.75 ms |
| 5,000 rows × 200 lookups | 574.82 ms | 578.08 ms |
| 10,000 rows × 200 lookups | 1913.02 ms | 1912.85 ms |
Pick by whether non-numeric entries should count.
| Situation | Use | Why |
|---|---|---|
| True mean of numeric readings | AVERAGE | Text labels must not drag the mean down. |
| Mean where blanks/text count as zero | AVERAGEA | AVERAGEA treats non-numeric entries as 0. |
| Average a column with stray text | AVERAGE | AVERAGE ignores the text; AVERAGEA would lower it. |
| Score sheet where TRUE=1 counts | AVERAGEA | AVERAGEA includes logical flags as 1/0. |
| Denominator-consistent manual mean | AVERAGE | Matches SUM/COUNT (numbers only). |
| Survey 'any response' averaging | AVERAGEA | AVERAGEA counts text responses as 0 in the mean. |
Common averaging tasks, side by side.
| Task | AVERAGE / old way | AVERAGEA | What changed |
|---|---|---|---|
| Mean of mixed readings | =AVERAGE(B2:B7) | =AVERAGEA(B2:B7) | AVERAGEA counts text/empty as 0 and TRUE as 1, lowering the mean. |
| Manual mean (numbers only) | =SUM(B2:B7)/COUNT(B2:B7) | =SUM(B2:B7)/COUNTA(B2:B7) | The COUNTA version mirrors AVERAGEA's broader denominator. |
| Mean ignoring text | =AVERAGE(B2:B7) | =AVERAGE(IF(ISNUMBER(B2:B7),B2:B7)) | AVERAGE already ignores text; the array form is redundant. |
| Coerce flags then average | =AVERAGE(B2:B7) | =AVERAGEA(B2:B7) | AVERAGEA folds TRUE/FALSE into 1/0 automatically. |