MAXA

What it does

MAXA(value1, [value2], ...)

MAXA(value1, [value2], …) returns the largest value in a set, where logical values and text are converted to numbers rather than skipped.

• "Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero)."
• "Logical values and text representations of numbers that you type directly into the list of arguments are counted."
• "If an argument is an array or reference, only values in that array or reference are used. Empty cells and text values in the array or reference are ignored."
• "If the arguments contain no values, MAXA returns 0 (zero)."
• "Arguments that are error values or text that cannot be translated into numbers cause errors."

The consequence that matters, and the reason the function exists: because text and FALSE convert to 0 rather than being skipped, MAXA can never return a negative number when any text or FALSE is present in the data. A column of negative measurements with one "n/a" typed into it has MAXA equal to 0, and the 0 is not in the data. MAXA is therefore not a selector in the sense MAX is: its result need not be one of its inputs.

That is a feature when the zeros are meaningful — "no reading" genuinely means zero — and a trap otherwise. It is the reason MAXA should be chosen deliberately rather than reached for as "MAX but more inclusive".

Arguments

Argument boundaries carry no meaning; all slots pool into one reduction.

Try it

Quirks

The coercion table, and a contradiction in it

Read as a table, Microsoft's rules give:

The two "see below" cells are a documented contradiction, and the Handbook publishes it as a finding rather than choosing a side. Microsoft's own page says both of the following about text inside a reference:

1. "Arguments that contain text or FALSE evaluate as 0 (zero)."
2. "Empty cells and text values in the array or reference are ignored."

These cannot both hold for text in a reference. If text evaluates as 0 it is counted and drags the maximum up toward zero; if it is ignored it contributes nothing and the maximum of an all-negative column stays negative. The two readings differ on exactly the case that distinguishes MAXA from MAX, and they differ in the sign of the answer.

The Handbook has not observed which reading Excel implements, and does not assert one from memory. What can be said:

• Rule 1 is the one that gives MAXA a reason to exist as a separate function; under rule 2 applied to text, MAXA and MAX would differ only on logicals in references.
• The A-suffix family's stated purpose — Microsoft's MAX page points at MAXA as the way to "include logical values and text representations of numbers in a reference as part of the calculation" — is consistent with rule 1.
• Nothing in the Handbook's record settles it, and the probe is one cell wide. It is first on the list below.

Result and edge cases

Returns Number. Unlike MAX, the result need not be one of the input values, because text and FALSE are converted rather than selected.

• An all-text column. Under rule 1 above the answer is 0; under rule 2 it is the no-values case, which is also 0. The two readings happen to coincide here, which is why this input does not discriminate between them. The discriminating input is an all-negative numeric column with one text cell in it.
• TRUE in a reference is counted as 1 — this part of the family's purpose is not in dispute, and it is the cleanest observable difference from MAX.
• Arrays are consumed by scanning, not lifted elementwise.

Errors

There is no documented #NUM! or #DIV/0! condition, and no documented empty-input error — the documented empty-input answer is 0.

How to implement

Numerical notes

There is no floating-point arithmetic in MAXA beyond the conversions, so there is no rounding error to analyse. Three implementation observations are worth recording anyway.

The empty-set identity is 0, and it is not −∞. The same associativity trap described on the MAX page applies here and is worse, because MAXA reaches 0 through two independent routes — the no-values rule and the text-converts-to-zero rule. A hierarchical computation that takes MAXA of column MAXAs over all-negative data will report 0 if any column is empty or contains any text. An implementer should track "have I seen an admissible value" as a separate flag from the running best, and must not initialise the accumulator to 0.

Rust code

aggregate(MAXA, "MAXA", VALUES, maxa)

/// MAXA: MAX, with held text read as 0 and held logicals as 1 and 0.
fn maxa(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    extreme(MAXA, Held::Values, |n, best| n > best, arguments, services)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• MAX — the numbers-only sibling, and the function whose own documentation names MAXA as the remedy for wanting logicals and numeric text counted. Read both coercion tables side by side before choosing; they differ on four rows.
• MINA — the mirror. Note that the sign asymmetry is worse for MINA: text and FALSE converting to 0 puts a ceiling of zero on MINA over all-positive data, which is the more frequently encountered version of the trap.
• The rest of the A suffix family: AVERAGEA, COUNTA, STDEVA, STDEVPA, VARA, VARPA. These share an intent — count more kinds of value — and do not share one uniform rule. COUNTA in particular counts anything non-empty including text, without converting it. Each page states its own table; do not generalise from this one.
• LARGE — the order-statistic selector, numbers only.
• Confused with: MAX, in both directions. A workbook that switched from MAX to MAXA to "make it handle the text cells" has changed the answer on every column containing text, usually by clamping it at zero.

Named variants

No named variants are recorded yet.