MAX vs LARGE: the biggest, or the nth biggest
MAX answers one question; LARGE answers a family of them: LARGE(range,1) IS the max, LARGE(range,2) the runner-up, and so on down. The subtlety worth knowing is how ties count — duplicates occupy their own ranks.
The differences at a glance
| MAX | LARGE | MIN | SMALL | |
|---|---|---|---|---|
| Returns | The single largest value | The k-th largest | The single smallest | The k-th smallest |
| With ties (10,20,20,30) | 30 | LARGE(...,2)=20 and LARGE(...,3)=20 — duplicates each take a rank | 10 | same behavior mirrored |
| Top-3 sum idiom | — | SUMPRODUCT(LARGE(rng,{1,2,3})) | — | SMALL for bottom-3 |
| Conditional versions | MAXIFS | FILTER + LARGE (or TAKE(SORT(...))) | MINIFS | FILTER + SMALL |
| Compatibility | Universal | Universal | Universal | Universal — all verified by execution |
Which should you use?
- MAX / MIN — Single extremes — latest date, record high, cheapest option — and their conditional MAXIFS/MINIFS forms.
- LARGE / SMALL — Podiums and leaderboards (k = 1,2,3), 'sum of best N scores' grading, trimmed statistics that drop the extremes (average between SMALL(...,2) and LARGE(...,2)), percentile-ish cutoffs on small data.
Compatibility (from executed tests)
All four execute identically in every version of all three apps we test, including tie handling and array-k forms like LARGE(range,{1,2,3}). On modern versions TAKE(SORT(range,-1),3) spills the actual top-3 values as a list — LARGE remains the portable classic.
Example formulas
| Runner-up | =LARGE(A2:A100,2) |
| Sum of best 3 scores | =SUMPRODUCT(LARGE(A2:A100,{1,2,3})) |
| Record high (conditional) | =MAXIFS(A:A,B:B,"East") |
Full per-version details on each function page: MAX · LARGE · MIN · SMALL.