Totals and counts function
MAX
The largest number.
Purpose
Find the largest figure in a range
Returns
The largest number, ignoring text and blanks
Syntax
=MAX(range1, [range2], ...)
| Argument | Required | What goes here |
|---|---|---|
range1 | Required | The cells to look at. |
range2 | Optional | The cells to look at. |
How to use MAX
MAX returns the largest number it can find. It is the fastest way to size an exposure: the biggest single item is the one that matters most if it goes wrong.
It tells you the amount and not which row it is on. INDEX and MATCH around it turn the figure into the invoice.
Dates here are text in the form 2026-10-31, which sorts in date order, so MAX over a date column gives the latest date.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=MAX(E2:E9)The largest invoice on the ledger. | 2450 |
=MAX(D2:D9)The oldest debt, in days. | 91 |
=INDEX(A2:A9,MATCH(MAX(E2:E9),E2:E9,0))Which invoice that largest figure belongs to. MAX finds the amount, MATCH finds its row, INDEX reads the reference off it. | INV-8803 |
=ROUND(MAX(E2:E9)/SUM(E2:E9),3)What share of the ledger sits in one invoice. This is the number that says whether the total is concentrated. | 0.312 |
Notes
- MAX over an empty range returns nought rather than an error, which can read as a real answer.
- MAXIFS narrows it to rows meeting a condition.
Related
Practise MAX in Unit 13
A reference tells you what it does. A unit makes you use it.