Statistical
MAX and MIN in Excel:syntax and examples
MAX returns the highest number in a range and MIN the lowest. Spanish Excel uses the same names, MAX and MIN. Syntax: =MAX(number1, [number2], ...).
Syntax
=MAX(number1, [number2], ...) / =MIN(number1, [number2], ...)In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
number1 | Required. The range, cell or number where you look for the highest or lowest. Example: D2:D6. |
number2, ... | Optional. More ranges, cells or numbers, up to 255. |
How do you use MAX and MIN? A step-by-step example
MAX gives you the highest number in a range and MIN the lowest. In this example you have a stationery catalog and you want the most expensive product and the cheapest.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Product | Category | Price |
| 2 | P-101 | Stapler everyone borrows | Office | 4.95 |
| 3 | P-102 | A5 notebook | Stationery | 2.30 |
| 4 | P-103 | Markers (12 colors) | Stationery | 6.80 |
| 5 | P-104 | Office chair | Furniture | 89.00 |
| 6 | P-105 | Desk lamp | Furniture | 24.50 |
=MAX(D2:D6) returns 89 and =MIN(D2:D6) returns 2.3.
- Type
=MAX(or=MIN(. - Select the column of prices, D2:D6.
- Close the parenthesis and press Enter.
How do you find which item is the maximum?
MAX returns 89, but it does not say it is the chair. To bring back the name, look that value up in the column of prices:
| Excel | Formula | Result |
|---|---|---|
| Microsoft 365 or 2021+ | =XLOOKUP(MAX(D2:D6),D2:D6,B2:B6) | Office chair |
| Any version | =INDEX(B2:B6,MATCH(MIN(D2:D6),D2:D6,0)) | A5 notebook |
If two products tie for the maximum, both formulas return the first one in the list. They are explained in XLOOKUP and VLOOKUP.
Do MAX and MIN count blanks, zeros and text?
| The cell holds | Does it count? |
|---|---|
| A number, including 0 | Yes |
| Nothing | No |
| Text | No |
| An error (#N/A, #DIV/0!) | The result is that error |
One case that confuses people: if the range has no numbers at all, MAX and MIN return 0, not an error.
What about the second-highest or a maximum with a condition?
| You want | Formula | Result |
|---|---|---|
| The second-highest price | =LARGE(D2:D6,2) | 24.5 |
| The second-lowest price | =SMALL(D2:D6,2) | 4.95 |
| The highest price in Furniture | =MAXIFS(D2:D6,C2:C6,"Furniture") | 89 |
How do MAX and MIN show up in an Excel test?
In a job interview the usual question is not the value but who: "Which salesperson sold the most this month?" The answer they expect combines MAX with a lookup, as in the table above.
In a skills test:
A1:A5 holds 4, an empty cell, 0, the text "n/a" and 7. What does
=MIN(A1:A5)return? a) 4 · b) 0 · c) An empty cell · d) #VALUE!
Answer: b. MIN ignores the empty cell and the text, but the 0 counts. For the mean of the same range, see AVERAGE; for the total, SUM.
Practice MAX and MIN
The English exercises are on the way. Until then, copy the catalog into a blank sheet, find the highest and lowest price, and then bring back the name with each formula in the table.
Available in: all versions of Excel. MAXIFS and MINIFS, in Excel 2019 and later. Sources: Microsoft: MAX function and MIN function. Results calculated with Formulo's engine.
Common mistakes
- MIN returns 0
- There is a 0 in the range, and it counts. Empty cells do not: with the scores 8, 6, blank, 9 and 5, MIN returns 5; with a 0 in place of the blank, it returns 0. To ignore zeros, in Excel 2019 or later: =MINIFS(C2:C6,C2:C6,">0") returns 5 on the column that has the 0.
- MAX returns 0 on a range that looks like it has numbers
- The numbers are stored as text, or the range is empty. MAX of a range with no numbers returns 0, not an error. Convert the column to numbers.
- You get the number, but you want to know whose it is
- MAX and MIN only return the value. To bring back the name, combine them with XLOOKUP or with INDEX and MATCH (shown on this page).
Frequently asked questions
What are MAX and MIN called in Spanish?
The same: MAX and MIN. Only the separator changes: =MAX(D2;D6) in a Spanish Excel.
How do I get the second-highest value?
With LARGE: =LARGE(D2:D6,2). For the second lowest, SMALL.
How do I get the maximum with a condition?
With MAXIFS, available in Excel 2019 and later: =MAXIFS(D2:D6,C2:C6,"Furniture").
Does MAX count empty cells?
No. Neither MAX nor MIN counts empty cells or text in a range.
What does MAX return if there is an error in the range?
The same error. If a cell in the range has #DIV/0!, MAX gives #DIV/0!. Fix the error in the source cell.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.