Excel MIN and MAX Functions: Find the Smallest and Largest Value
Return the smallest and largest numbers in a range.
MIN returns the smallest number in what you give it and MAX returns the largest. Both skip text, blanks and logical values entirely, which is what makes them safe to point at a column that also contains a header or a stray label.
Their less obvious use is capping. =MIN(A2, 100) returns whichever is smaller, so it clamps a value to a ceiling of 100; =MAX(A2, 0) clamps it to a floor of zero, which is the standard way of stopping a calculated figure going negative. Nesting them — =MIN(MAX(A2, 0), 100) — holds a value inside a band.
For extremes under a condition, MINIFS and MAXIFS follow the SUMIFS pattern exactly: the range to examine first, then pairs of criteria range and criteria.
Syntax
=MIN(number1, [number2], …) =MAX(number1, [number2], …)Arguments
number1Required- The first range or value. Text, blanks and logicals in it are ignored.
number2, …Optional- Further ranges or values, up to 255. They can be scattered.
The example data
Headers in row 1, data in A2:D6. C4 is blank and C6 holds text.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sensor | Reading | Calibration | Batch |
| 2 | North inlet | 17 | 3 | 12 |
| 3 | South inlet | -4 | 5 | 7 |
| 4 | Header tank | 63 | 12 | |
| 5 | Overflow | 8 | 2 | 9 |
| 6 | Return line | -21 | n/a | 7 |
Worked examples
=MIN(B2:B6)Result: -21
The smallest reading, negative included.
=MAX(B2:B6)Result: 63
The largest. Both ignore the blank and the text in column C if pointed there.
=MAX(B2, 0)Result: 17
Clamping to a floor of zero. On row 3, where the reading is -4, this would return 0.
=MINIFS(B2:B6, D2:D6, 12)Result: 17
The smallest reading in batch 12. MINIFS puts the range to minimise first, like SUMIFS.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Returns 0 on a range that has values
Why it happens: Every number in the range is stored as text, so both functions skip all of them and fall back to 0.
How to fix it: Text numbers align left by default. Convert the column with VALUE or Text to Columns.
Ignores a value you wanted counted
Why it happens: MIN and MAX skip TRUE/FALSE and text, even when the text looks numeric.
How to fix it: MINA and MAXA include logicals and treat text as 0, if that is genuinely what you want.
#VALUE!
Why it happens: A referenced cell holds an error, which propagates.
How to fix it: Wrap the source in IFERROR, or find and fix the upstream error — usually better.
Tips worth knowing
- =MIN(MAX(value, floor), ceiling) holds a number inside a band in one formula.
- MINIFS and MAXIFS need Excel 2019+; before that, an array MIN(IF(...)) was the only route.
- To find *which* row holds the max, pair it with MATCH: =INDEX(A2:A6, MATCH(MAX(B2:B6), B2:B6, 0)).
- MAX over an empty range returns 0, not an error — worth guarding in a template.
Frequently asked questions
How do I find the largest value that meets a condition?
Use MAXIFS: =MAXIFS(B2:B100, D2:D100, 12) returns the largest reading in batch 12. The range you are taking the maximum of comes first, then criteria range and criteria pairs, exactly like SUMIFS.
Do MIN and MAX ignore blank cells?
Yes. Blanks, text and logical values are all skipped. This means MIN over a column with one number and ninety-nine blanks returns that number rather than 0.
How do I stop a formula returning a negative number?
Wrap it in MAX with zero: =MAX(your_formula, 0). Whichever is larger wins, so anything below zero becomes zero. The mirror trick with MIN caps an upper bound.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.