MAX

What it does

MAX(number1, [number2], ...)

MAX(number1, [number2], …) returns the largest of the numeric values it admits.

As mathematics this is the top order statistic, x₍ₙ₎ = max{x₁ … xₙ} — the supremum of a finite set, which is attained and is therefore one of the inputs. MAX is a selector: its result is bit-identical to one of the values it was given, it performs no arithmetic, and it introduces no rounding error of its own.

Because there is no arithmetic, everything genuinely interesting about MAX lives in one question: which values count? That question has two different answers depending on how the value reached the function, and Microsoft documents both.

max is associative, commutative and idempotent, with identity −∞; it is a monoid fold. The identity is where the specification has to make a choice, because Excel has no −∞ in its value universe. Microsoft's choice is documented and stated below.

Arguments

Argument boundaries carry no meaning: MAX(A1:A10), MAX(A1:A5, A6:A10) and MAX(A1, A2, …, A10) all reduce over one pooled set. There is no per-argument grouping and no short-circuiting.

Try it

Quirks

The two coercion policies

This is the section that earns the page, and it is quoted rather than paraphrased because the distinction is exact. Microsoft's MAX page states:

• "Arguments can either be numbers or names, arrays, or references that contain numbers."
• "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 numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored."
• "If the arguments contain no numbers, MAX returns 0 (zero)."
• "Arguments that are error values or text that cannot be translated into numbers cause errors."

Read as a table:

So MAX(TRUE, 0) is 1 while MAX(A1:A2) with TRUE in A1 and 0 in A2 is 0. Same values, same function, different boundary, different rule. This is the flagship asymmetry of Coercion and lifting, and MAX is one of the cleanest places to see it, because both branches are documented on the same page in consecutive sentences.

The practical consequence is that MAX over a column cannot be made to see logicals or numeric text. Microsoft's own page names the remedy: "If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function." See MAXA, and read its own coercion rules before switching, because they are not simply "the same but including text".

Result and edge cases

Returns Number.

• No numbers among the arguments. Microsoft documents: "If the arguments contain no numbers, MAX returns 0 (zero)." So MAX over a wholly blank range is documented to be 0, not an error and not the empty value.

• A single value. MAX(x) is x, including for the largest finite double and the smallest subnormal, which is a useful check that no normalisation is applied on the way out.

• +0 and −0. IEEE comparison calls these equal, so which one is returned is a tie-breaking decision the specification does not make. MAX(0, −0) may legitimately return either representation, and the two are distinguishable through the sign bit and through 1/x. Unverified.

• Arrays are consumed by scanning, not lifted elementwise; MAX is not a lift kernel.

• Dates and times are numbers wearing formats, so MAX over a date column returns the latest date — correctly, and with the format usually carried through by the cell rather than by the function.

Errors

There is no documented #NUM! or #N/A condition. Note the absence of an "empty input" error in the documentation — the documented answer for that case is 0, as discussed above.

How to implement

Numerical notes

MAX has no arithmetic and therefore no rounding error. The engineering questions are three, and each of them is about definition rather than precision.

1. The comparison predicate. A maximum needs a total order. IEEE-754 > is a total order on the doubles Excel can hold, with the single exception that +0 and −0 compare equal — Excel has no NaN in its value universe, so the usual NaN-poisoning problem does not arise at this level. Whether Excel's MAX uses raw > or the tolerant, truncation-style 15-significant-digit comparison it applies in some other families is a genuine open question; for MAX a tolerant comparator would only affect which of two near-equal representations is returned, so the consequence is smaller than it is for lookups, but it is observable.

3. The empty-set identity. Mathematically max ∅ = −∞, which Excel cannot represent, so the specification substitutes 0. That substitution is not innocuous: it makes MAX non-associative across the empty case in a way that matters for incremental computation. If a range is empty, MAX returns a value inside the data's range rather than below it, so MAX(MAX(A), MAX(B)) is not MAX(A ∪ B) when one of A or B is empty and the other is entirely negative. A workbook that computes column maxima and then takes the maximum of those will report 0 for a set of all-negative data with one empty column. That is a real, silent correctness trap and it follows directly from the documented rule.

For an implementer: initialise the accumulator with a sentinel and a found flag, never with 0 or with f64::NEG_INFINITY alone — the first is wrong for all-negative data and the second produces −∞, which is outside Excel's value universe. Track "have I seen a number" separately from "what is the best so far", and apply the documented zero only at the end.

Rust code

aggregate(MAX, "MAX", NUMBERS, max)

/// MAX: the largest number, or 0 when there is none.
fn max(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    extreme(MAX, Held::Numbers, |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

• MIN — the exact mirror, same argument shape, same coercion table, same documented zero for the no-numbers case. Any behavioural difference between MAX and MIN beyond the comparator is a defect in one of them.
• MAXA — the variant that counts text and logicals inside references, named by Microsoft's own MAX page as the remedy. The A suffix family (MAXA, MINA, AVERAGEA, COUNTA, STDEVA, VARA) shares that intent and does not share one uniform rule; each page states its own.
• MAXIFS — the conditional form, which selects the population by criteria before taking the maximum. Note that MAXIFS returns 0 when no cell meets the criteria, which is the same convention the MAX documentation states for the empty case.
• LARGE — the general order-statistic selector; LARGE(array, 1) is the maximum over the same admitted set, but LARGE takes exactly one array and errors on an empty one instead of returning zero. The pair MAX/LARGE is the cleanest illustration of the empty-set convention differing between two functions that compute the same thing.
• DMAX — the database-family maximum, with criteria-range selection.
• SUBTOTAL (function numbers 4 and 104) and AGGREGATE (function number 4) — the filter-aware and error-skipping maxima. AGGREGATE is the only route to a maximum that ignores error values, which MAX cannot do.
• Confused with: MAX and LARGE on ranges containing text (the same scan policy governs both, so they should agree — a disagreement is diagnostic); and with the idea that MAX over an empty range is an error, which the documentation contradicts.

Named variants

No named variants are recorded yet.