MAXA
Returns the largest value in a set, treating text as 0 and TRUE as 1, FALSE as 0. Unlike MAX which ignores text and logical values.
MAXA(value1, [value2], ...)Arguments
value1First value, reference, or range.value2Optional. Up to 254 additional values, references, or ranges.(optional)
=MAXA(-1, -2, "text")Text counts as 0, which is greater than -1 and -2
=MAXA(0.5, TRUE)TRUE counts as 1, which is greater than 0.5
- •Use MAX if you want to ignore text and logical values
- •Empty cells are ignored
- •Returns 0 if all values are non-numeric and not logical
- •Forgetting that MAXA treats text as 0, which can produce a surprisingly high result if a range of otherwise negative numbers contains stray text - the text's value of 0 becomes the maximum
- •Confusing MAXA with MAX - MAX ignores text and logical values completely, while MAXA includes them using the 0/1 conversion rules, and choosing the wrong one changes the result whenever non-numeric entries are present
- •Expecting MAXA to return an error or blank when the range has no genuine numbers - it returns 0 in that case, treating any text found as the maximum value of 0
MAXIgnores text and logical values entirely rather than converting them to 0 or 1 like MAXA, giving a different result whenever non-numeric entries are present.MINAApplies the same text-as-0, TRUE-as-1 conversion rules as MAXA, but to find the smallest value instead of the largest.LARGEFinds the nth largest value in a purely numeric range, a different tool than MAXA when text/logical inclusion isn't relevant.Why did MAXA return 0 when all my numbers are negative?
The range likely contains a text entry, which MAXA treats as 0 - since 0 is greater than any negative number, it becomes the maximum.
What's the difference between MAXA and MAX?
MAX ignores text and logical values completely, finding the largest value only among genuine numbers. MAXA instead converts TRUE to 1, FALSE to 0, and any text to 0, including them in the comparison.
What does MAXA return if there are no numeric values at all?
0, since any text encountered is treated as 0, and 0 becomes the maximum by default when nothing larger is found.
Need to translate a formula using MAXA?
Use our translator to convert your complete formula