Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Statistical

MAX

MAX in Dutch Excel

Returns the largest number among its arguments, ignoring blanks and text in ranges.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Entry no.	Journal	Debit	Largest debit
2	25-0412	VRK	€12,450.00€ 12.450,00	€15,200.00€ 15.200,00=MAX(C2:C7)=MAX(C2:C7)
3	25-0413	INK	€8,730.00€ 8.730,00
4	25-0414	BNK	€15,200.00€ 15.200,00
5	25-0415	MEM	€9,875.00€ 9.875,00
6	25-0416	INK	€14,980.00€ 14.980,00
7	25-0417	VRK	€6,120.00€ 6.120,00

Result: €15,200.00€ 15.200,00

=MAX(C2:C7)

=MAX(C2:C7)

What MAX does and when to use it

What it does: it returns the largest number you give it, for example the highest single posting in a ledger or the latest date.

When to use it: for peaks and latest dates. With a fixed number as one argument it sets a floor, as in MAX(0, x), so overtime or amounts owed never go negative. For the largest value under conditions use MAXIFS, for the second or third largest use LARGE.

How Excel evaluates it

Excel collects the values from every argument and keeps the largest:

from a range it takes only numbers; blanks, text and TRUE or FALSE are left out, including numbers stored as text
a value typed directly into the formula is converted if it can be read as a number
if any cell holds an error value, MAX returns that error

Watch out: If MAX finds no numbers, it returns 0 and not an error, so a 0 does not prove that the largest value is 0.

Mental model

A high-jump bar that only moves up: each value that clears it raises the bar, and whatever the bar rests on at the end is the answer. If no one jumps, the bar stays on the ground at 0.

Terms used on this page: error value, argument, range, cell

Syntax

Official MAX documentation

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

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

Arguments
Argument	Type	Meaning
`number1`	Required	The first number, cell or range to compare.
`number2`	Optional	More numbers, cells or ranges; a fixed number here acts as a floor.

Return value:A number: the largest value in the arguments

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Largest single debit posting

You have exported six journal lines from the XAF auditfile of Veldhuis Installatie BV. Before choosing items for testing, you want the largest single debit amount, to compare it with performance materiality. The amounts are close together, so reading it off by eye is risky.

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Entry no.	Journal	Debit	Largest debit
2	25-0412	VRK	€12,450.00€ 12.450,00	€15,200.00€ 15.200,00=MAX(C2:C7)=MAX(C2:C7)
3	25-0413	INK	€8,730.00€ 8.730,00
4	25-0414	BNK	€15,200.00€ 15.200,00
5	25-0415	MEM	€9,875.00€ 9.875,00
6	25-0416	INK	€14,980.00€ 14.980,00
7	25-0417	VRK	€6,120.00€ 6.120,00
8

Formula cell (E2)Input

Formula in E2

=MAX(C2:C7)

=MAX(C2:C7)

Result: €15,200.00€ 15.200,00

Step by step

Excel reads C2:C7 and finds six numbers: 12450, 8730, 15200, 9875, 14980 and 6120.
It starts with 12450 as the largest so far. 8730 is smaller and 15200 is larger, so 15200 takes over.
9875, 14980 and 6120 are each compared with 15200. None is larger; 14980 in C6 comes within 220 (15200 - 14980).
MAX returns 15200, the debit of entry 25-0414 in the BNK journal (cell C4).
Change C6 to 15300 and E2 becomes 15300 without touching the formula.

Watch out: If an amount came out of the export as text, MAX skips it and could return a lower figure without any sign of trouble. A left-aligned number in a right-aligned column is the clue; COUNT(C2:C7) should be 6.

Days overdue that never go negative

For the receivables aging list of Veldhuis Installatie BV you have the age of each open invoice in days. An invoice is overdue after the payment term in E1. An invoice that is not yet due has zero days overdue, not a negative number, otherwise it offsets the late invoices in a total. The formula in C2 is copied down.

Sheet excerpt: C2 holds the formula
Row	A	B	C	D	E
1	Invoice no.	Age (days)	Days overdue	Payment term (days)	3030
2	F25-0815	9595	6565=MAX(0, B2 - $E$1)=MAX(0; B2 - $E$1)
3	F25-1041	1212
4	F25-0902	3030
5	F25-0987	4747
6	F25-0774	2929
7

Formula cell (C2)Input

Formula in C2

=MAX(0, B2 - $E$1)

=MAX(0; B2 - $E$1)

Result: 6565

Step by step

Excel evaluates the second argument first: B2 - $E$1 = 95 - 30 = 65.
MAX compares 0 with 65 and returns the larger: 65.
Copied to C3, the formula reads B3 but still $E$1: 12 - 30 = -18. MAX compares 0 with -18 and returns 0. Without MAX, C3 would show -18 days overdue.
The next three rows give:
- C4: 30 - 30 = 0, so MAX(0, 0) = 0
- C5: 47 - 30 = 17
- C6: 29 - 30 = -1, so 0
The column adds up to 65 + 0 + 0 + 17 + 0 = 82 days overdue. Without the floor it would add up to 65 - 18 + 0 + 17 - 1 = 63, because the invoices that are not yet due would cancel out 19 days of the late ones.

Watch out: Without the $ signs, C3 would subtract E2, an empty cell treated as 0, and report 12 days overdue for an invoice that is not yet due.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

MAX does not care that one argument is a constant: it compares 0 and the difference like any two numbers, so the result can never fall below 0. The $ signs keep every copy pointed at E1, so changing the payment term in one place updates the whole column.

More to watch out for

An invoice whose age is missing gets MAX(0, 0 - 30) = 0, which looks like 'not overdue' rather than 'no data'. Check the list for blanks before you rely on the totals.

Variations

=MAX(0, $E$1 - B2)

=MAX(0; $E$1 - B2)

The mirror question, days left before the invoice is due: 0 for F25-0815, 18 for F25-1041. The floor works the same in both directions.

AdvancedExcel 2019 or later (This example needs Excel 2019 or later)

Latest manual posting on one account

For the cut-off test around year end 31 December 2025 you want to know the latest posting date of manual memorial entries (journal MEM) on one general ledger account. Postings from other journals do not count. If no MEM entry exists on that account, the cell must say so instead of showing a date in 1900.

Sheet excerpt: G3 holds the formula
Row	A	B	C	F	G
1	Journal	Account	Date	Journal	MEM
2	MEM	13001300	18 Dec 2025	Account	13001300
3	VRK	13001300	4 Jan 2026	Latest posting	3 Jan 20263 Jan 2026=IF(COUNTIFS(A2:A9, G1, B2:B9, G2) = 0, "No entries", MAXIFS(C2:C9, A2:A9, G1, B2:B9, G2))=IF(COUNTIFS(A2:A9; G1; B2:B9; G2) = 0; "No entries"; MAXIFS(C2:C9; A2:A9; G1; B2:B9; G2))
4	MEM	45004500	29 Dec 2025
5	MEM	13001300	3 Jan 2026
6	BNK	11001100	30 Dec 2025
7	MEM	13001300	23 Dec 2025
8	INK	16001600	6 Jan 2026
9	MEM	40004000	31 Dec 2025
10

Formula cell (G3)Input

Formula in G3

=IF(COUNTIFS(A2:A9, G1, B2:B9, G2) = 0, "No entries", MAXIFS(C2:C9, A2:A9, G1, B2:B9, G2))

=IF(COUNTIFS(A2:A9; G1; B2:B9; G2) = 0; "No entries"; MAXIFS(C2:C9; A2:A9; G1; B2:B9; G2))

Result: 3 Jan 20263 Jan 2026

Step by step

Excel evaluates the test inside IF first. COUNTIFS counts rows where A is MEM and B is 1300: rows 2, 5 and 7. The count is 3, so the test 3 = 0 is FALSE.
Because the test is FALSE, IF evaluates its third argument, MAXIFS, with the same two conditions.
MAXIFS keeps the dates in C2:C9 of the matching rows:
- 18 December 2025 (serial 46009)
- 3 January 2026 (serial 46025)
- 23 December 2025 (serial 46014)
The VRK posting on account 1300 (4 January) and the MEM postings on other accounts are excluded by the conditions.
The largest serial number is 46025, so G3 shows 3 January 2026: a manual posting after year end on account 1300 that you follow up in the cut-off test.
Type 1500 in G2. COUNTIFS returns 0, the test is TRUE, and IF returns No entries. MAXIFS is not evaluated at all.
Without the IF, MAXIFS for account 1500 would return 0. In a date-formatted cell that shows as 00-01-1900 or 1/0/1900, a value that looks like a data glitch rather than 'no manual entries'.

Watch out: Testing MAXIFS(...) = 0 instead of counting works for dates, but not for values that can genuinely be 0 or negative, such as a balance. Counting is the safe habit.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Dates are serial numbers, so the largest date is the latest one, and MAXIFS applies AND conditions exactly like SUMIFS. MAXIFS returns 0 for an empty selection instead of an error, so the only reliable test for 'no match' is to count the matches first with the same conditions.

More to watch out for

If the conditions in COUNTIFS and MAXIFS drift apart after an edit, the check tests one thing and the result reports another. Keep them identical, or use the LET variation.
MAXIFS needs Excel 2019 or later. In older versions the formula shows #NAME?.

Variations

=MAX(FILTER(C2:C9, (A2:A9 = G1) * (B2:B9 = G2)))

=MAX(FILTER(C2:C9; (A2:A9 = G1) * (B2:B9 = G2)))

With FILTER, an empty selection gives #CALC! instead of 0, so an account without MEM entries is at least visible; wrap it in IFERROR for a message.

=LET(
  hits, COUNTIFS(A2:A9, G1, B2:B9, G2),
  latest, MAXIFS(C2:C9, A2:A9, G1, B2:B9, G2),
  IF(hits = 0, "No entries", latest)
)

=LET(
  hits; COUNTIFS(A2:A9; G1; B2:B9; G2);
  latest; MAXIFS(C2:C9; A2:A9; G1; B2:B9; G2);
  IF(hits = 0; "No entries"; latest)
)

The same logic with named parts, so the two sets of conditions sit next to each other and are simpler to keep in step.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Largest jump in entry numbering

For the completeness test of the purchase journal (INK) you check that entry numbers run consecutively. You want the largest jump between two consecutive entry numbers: a jump of 1 is normal, a larger one means entries are missing from the auditfile, and you ask the client about them. The export is sorted by sequence number.

Sheet excerpt: E2 holds the formula
Row	A	B	C	E
1	Sequence no.	Date	Debit	Largest jump
2	12011201	3 Nov 2025	€3,480.00€ 3.480,00	44=LET( earlier, A2:A7, later, A3:A8, gaps, later - earlier, IF(COUNT(A2:A8) < 2, 0, MAX(gaps)) )=LET( earlier; A2:A7; later; A3:A8; gaps; later - earlier; IF(COUNT(A2:A8) < 2; 0; MAX(gaps)) )
3	12021202	5 Nov 2025	€12,600.00€ 12.600,00
4	12031203	10 Nov 2025	€845.00€ 845,00
5	12071207	24 Nov 2025	€5,200.00€ 5.200,00
6	12081208	26 Nov 2025	€2,310.00€ 2.310,00
7	12091209	2 Dec 2025	€7,790.00€ 7.790,00
8	12101210	4 Dec 2025	€1,560.00€ 1.560,00
9

Formula cell (E2)Input

Formula in E2

=LET(
  earlier, A2:A7,
  later, A3:A8,
  gaps, later - earlier,
  IF(COUNT(A2:A8) < 2, 0, MAX(gaps))
)

=LET(
  earlier; A2:A7;
  later; A3:A8;
  gaps; later - earlier;
  IF(COUNT(A2:A8) < 2; 0; MAX(gaps))
)

Result: 44

Step by step

LET names two offset views of the sequence column: earlier is A2:A7, the first six entries, and later is A3:A8, the last six. Row by row, later is always the entry that follows earlier.
gaps subtracts them element by element: 1202 - 1201 = 1, 1203 - 1202 = 1, 1207 - 1203 = 4, 1208 - 1207 = 1, 1209 - 1208 = 1, 1210 - 1209 = 1.
COUNT(A2:A8) is 7, which is not below 2, so IF evaluates MAX(gaps): the largest of 1, 1, 4, 1, 1, 1 is 4.
A jump of 4 means 4 - 1 = 3 numbers are missing, 1204, 1205 and 1206, between 10 and 24 November 2025. The formula found the jump; the missing numbers are what you ask the client about.
If the export held a single entry, COUNT would be 1 and the formula would return 0 instead of a meaningless difference with blank cells.

Watch out: The mirror-image mistake is MAX(A2:A8) - MIN(A2:A8) = 9, the span of the whole numbering. It is a plausible number and answers a different question: how many numbers the range covers, not how many are missing in one place.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Subtracting a range from the same range shifted by one row pairs every entry with the next one, and MAX then works on the array of jumps, not on the numbers. MAX over a computed array is the point here: it answers a question about the spacing of the data, which no column on the sheet holds.

Do not use this construction when the export mixes journals or is not sorted; the jumps would then pair unrelated entries. The alternative is a helper column with =A3 - A2 copied down and a MAX over it: more cells, but every jump is visible and can be ticked off in the audit file.

More to watch out for

If the list is not sorted, some jumps are negative. MAX ignores their size, so a big jump stored as -4 is missed and the result is silently too small.
One sequence number stored with a prefix, such as INK1203, makes its subtraction #VALUE!, and MAX passes that error on for the whole result. MAX only skips text in ranges it reads directly, not in arrays built from errors.

Variations

=MAX(A3:A8 - A2:A7)

=MAX(A3:A8 - A2:A7)

The bare version without LET and without the guard for an export with fewer than two entries.

Common mistakes

Common mistakes
Symptom	Cause	Fix
0 for a column that looks full	MAX of a column that looks full returns 0, because every value was imported as text.	Check with COUNT(range); if it is 0, convert the column to numbers.
A date in January 1900 from MAXIFS	MAXIFS finds no matching rows and returns 0, which a date format shows as a date in January 1900.	Test for matches first with COUNTIFS, as in the Advanced example.
The result never goes below 0	Writing MAX(B2 - E1, 0) and expecting the difference, then being surprised it never goes negative.	That is what the 0 does: it is a floor. Leave it out when negative values carry meaning, such as a variance.

Used in combinations

Gap test on invoice numbers with FILTER, SEQUENCE and XMATCHFind the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.Excel 2021 or later (This combination needs Excel 2021 or later)