AVERAGEA
Returns the average (arithmetic mean) of values, treating text as 0 and TRUE as 1, FALSE as 0. Unlike AVERAGE which ignores text and logical values, AVERAGEA includes them.
AVERAGEA(value1, [value2], ...)Arguments
value1First value, reference, or range to average.value2Optional. Up to 254 additional values, references, or ranges.(optional)
=AVERAGEA(10, 20, "text")(10 + 20 + 0) / 3 = 10 — text counts as 0
=AVERAGEA(5, TRUE, FALSE)(5 + 1 + 0) / 3 = 2
- •Use AVERAGE if you want to ignore text and logical values
- •Empty cells are still ignored (not counted as 0)
- •Returns #DIV/0! if no numeric/text/logical values are provided
- •Forgetting that AVERAGEA treats text as 0 rather than ignoring it, which can silently pull down an average if a range contains stray text entries that were meant to be excluded entirely
- •Confusing AVERAGEA with AVERAGE - AVERAGE ignores text and logical values completely, while AVERAGEA includes them using the 0/1 conversion rules, and choosing the wrong one changes the result whenever non-numeric entries are present
- •Assuming empty cells count as 0 in AVERAGEA - they're still ignored entirely, just like in AVERAGE, only genuine text and logical values get the special 0/1 treatment
AVERAGEIgnores text and logical values entirely rather than converting them to 0 or 1 like AVERAGEA, giving a different result whenever non-numeric entries are present.SUMAdds values together without averaging, sometimes combined with COUNTA to manually replicate AVERAGEA-style logic with more control.MAXAApplies the same text-as-0, TRUE-as-1 conversion rules as AVERAGEA, but to find the largest value instead of the average.How does AVERAGEA handle text values in the range?
It treats any text as 0, rather than ignoring it the way AVERAGE does - this can noticeably shift the result if your data includes stray text entries you actually wanted excluded.
What's the difference between AVERAGEA and AVERAGE?
AVERAGE ignores text and logical values completely, calculating the average only from genuine numbers. AVERAGEA instead converts TRUE to 1, FALSE to 0, and any text to 0, including them in the calculation.
Do AVERAGEA and AVERAGE ever give the same result?
Yes, whenever the range contains only numbers and no text or logical values - in that case there's nothing for AVERAGEA's special conversion rules to affect.
Need to translate a formula using AVERAGEA?
Use our translator to convert your complete formula