Statistics · Head-to-head

AVERAGE vs AVERAGEA

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.

fx
AVERAGE(…)  vs  AVERAGEA(…)
20 vs 10.17
Mixed-range mean
#DIV/0! / 0
On a text cell
#DIV/0! / 1
On TRUE
15 vs 7.5
Text lowers the mean
▶ Try both live See the feature matrix
🏆Verdict: What counts in the denominator

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.

AVERAGE strengths

  • Average of scores, prices, readings.
  • You want text labels and flags ignored.
  • Matching SUM(range)/COUNT(range).
  • The standard numeric mean.

AVERAGEA strengths

  • Absent entries should count as zero.
  • TRUE/FALSE flags should count as 1/0.
  • A survey where blank = 0 by design.
  • You deliberately want non-numbers in the denominator.

Feature-by-feature matrix

What each function includes in the mean.

CapabilityAVERAGEAVERAGEAWhy it matters
Averages numeric cells✓ Numbers only✓ Numbers includedBoth include 10, 20, 30.
Text in a range✗ Ignored✓ Counted as 0AVERAGEA adds a 0 and grows the denominator.
Logical TRUE in a range✗ Ignored✓ Counted as 1TRUE in B7 adds 1 to AVERAGEA only.
Empty string "" in a range✗ Ignored✓ Counted as 0"" lowers the AVERAGEA mean.
Truly blank cells✓ Skipped✓ SkippedNeither counts a genuinely empty cell.
No numbers in selection✓ #DIV/0!✓ Mean of coerced 0/1AVERAGE errors; AVERAGEA can return 0.
Literal text argument✓ #VALUE!✓ #VALUE!In HyperFormula a direct text arg errors in both; range text differs.

Syntax, side by side

number1… (numeric) vs value1… (text=0, TRUE=1).

AVERAGE

AVERAGE(number1, [number2], …)
number1A range or list of numeric values.
number2…Optional extra ranges or values.
=AVERAGE(B2:B7) → 20

AVERAGEA

AVERAGEA(value1, [value2], …)
value1A range or list; text counts as 0, TRUE as 1, FALSE as 0.
value2…Optional extra ranges or values.
=AVERAGEA(B2:B7) → 10.16666667

Live head-to-head playground

One editable mixed column, two means. Pick a scenario or type your own — watch text and TRUE drag the AVERAGEA mean down.

A1Sample data — click any cell to edit; both formulas re-evaluate live

AVERAGE

—

AVERAGEA

—
HyperFormula 3.5 · live

Every difference, measured

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.

Mixed range (numbers, text, empty-string, logical)

One range holding numbers, text, an empty string and a logical value.
AVERAGE
=AVERAGE(B2:B7)
20
AVERAGEA
=AVERAGEA(B2:B7)
10.16666667
AVERAGE uses 10,20,30 → 20. AVERAGEA adds hello=0, ""=0, TRUE=1 → 61/6 = 10.16666667.

Numeric-only selection (B2, B6)

A selection of only numeric cells, so both means agree.
AVERAGE
=AVERAGE(B2,B6)
20
AVERAGEA
=AVERAGEA(B2,B6)
20
Averaging only the numeric cells (10 and 30): both agree at 20.

A single text cell

A single text cell: ignored by AVERAGE, counted as 0 by AVERAGEA.
AVERAGE
=AVERAGE(B3)
#DIV/0!
AVERAGEA
=AVERAGEA(B3)
0
A lone text cell: AVERAGE has no numbers → #DIV/0!; AVERAGEA counts text as 0 → 0.

A single logical cell (TRUE)

A single TRUE cell: ignored by AVERAGE, counted as 1 by AVERAGEA.
AVERAGE
=AVERAGE(B7)
#DIV/0!
AVERAGEA
=AVERAGEA(B7)
1
A lone TRUE: AVERAGE ignores it → #DIV/0!; AVERAGEA counts TRUE as 1 → 1.

An empty-string cell ("")

A cell holding "": skipped by AVERAGE, counted as 0 by AVERAGEA.
AVERAGE
=AVERAGE(B5)
#DIV/0!
AVERAGEA
=AVERAGEA(B5)
0
An empty string "": AVERAGE ignores it → #DIV/0!; AVERAGEA counts it as 0 → 0.

A single number

A single numeric cell, where both agree.
AVERAGE
=AVERAGE(B2)
10
AVERAGEA
=AVERAGEA(B2)
10
A single number agrees: 10 and 10.

Text inside a range lowers the mean

A shorter range where one text cell visibly drags the AVERAGEA mean down.
AVERAGE
=AVERAGE(B2:B5)
15
AVERAGEA
=AVERAGEA(B2:B5)
7.5
Sub-range 10,"hello",20,""): AVERAGE=15 (2 numbers); AVERAGEA=7.5 (4 entries, text and "" as 0 → 30/4).

Engine benchmark

Engine cost averaging a mixed numeric/text/logical column (relative only, not Microsoft Excel).

Table sizeAVERAGEAVERAGEA
1,000 rows × 200 lookups67.98 ms60.75 ms
5,000 rows × 200 lookups574.82 ms578.08 ms
10,000 rows × 200 lookups1913.02 ms1912.85 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Both scan the same range once; absolute times are engine-specific and only meaningful as a relative signal.

Which one should you use?

Pick by whether non-numeric entries should count.

SituationUseWhy
True mean of numeric readingsAVERAGEText labels must not drag the mean down.
Mean where blanks/text count as zeroAVERAGEAAVERAGEA treats non-numeric entries as 0.
Average a column with stray textAVERAGEAVERAGE ignores the text; AVERAGEA would lower it.
Score sheet where TRUE=1 countsAVERAGEAAVERAGEA includes logical flags as 1/0.
Denominator-consistent manual meanAVERAGEMatches SUM/COUNT (numbers only).
Survey 'any response' averagingAVERAGEAAVERAGEA counts text responses as 0 in the mean.

AVERAGE → AVERAGEA migration

Common averaging tasks, side by side.

TaskAVERAGE / old wayAVERAGEAWhat 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.

Frequently asked questions

What is the difference between AVERAGE and AVERAGEA?
AVERAGE averages only numeric cells and ignores text/logical values. AVERAGEA counts text as 0 and TRUE/FALSE as 1/0, including them in the denominator. Measured on a mixed range: 20 vs 10.16666667.
Does AVERAGEA count text as zero?
Yes — text inside a range counts as 0 (measured: a range 10,20,30,"absent" gives AVERAGEA 15 vs AVERAGE 20). Note: a literal text argument like AVERAGEA(10,"x") is #VALUE! in HyperFormula; the text must be in a cell.
How does AVERAGEA treat TRUE and FALSE?
TRUE counts as 1 and FALSE as 0 (measured: AVERAGEA(TRUE)=1). AVERAGE ignores logical values entirely (AVERAGE(TRUE)=#DIV/0!).
Which matches SUM/COUNT?
AVERAGE. It uses only the numbers, exactly like SUM(range)/COUNT(range). AVERAGEA uses a larger denominator whenever non-numbers are present.
When should I use AVERAGEA?
Only when a non-numeric entry genuinely means zero — an absent score, a blank survey response counted as 0, or TRUE/FALSE flags you want averaged as 1/0. Otherwise use AVERAGE.