The MIN function in Excel
MIN returns the smallest number in a set of cells: the cheapest quote, the fastest time, the earliest date. Like MAX, it ignores empty cells and text in ranges. It also has a second job: MIN(value,limit) caps a number so it never goes above a limit, which is handy for reimbursements and commission caps.
Syntax
=MIN(number1, [number2], ...)
| number1 | The first number, cell or range to check, like B2:B6. |
|---|---|
| number2, ... | Optional. More numbers or ranges. MIN returns the smallest value across all of them. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
Meals are reimbursed up to the limit in F1. In C2, use MIN to work out what to pay Ana: her claim in B2, or the limit if the claim is higher.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Reimbursed | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: Give MIN the two candidates: the claim and the limit. The smaller one is what gets paid.
Examples
=MIN(B2:B6)→ 3950The lowest quote. Dunmore hasn't replied yet, and its empty cell is skipped rather than treated as 0.
=XLOOKUP(MIN(B2:B6),B2:B6,A2:A6)→ "Brightbox"Finds the lowest quote, then returns the supplier who sent it.
=MIN(C2:C6)→ 9The shortest lead time, which belongs to a different supplier than the cheapest quote.
Example data
| 1 | Supplier | Quote | Lead time (days) |
|---|---|---|---|
| 2 | Atlas Packaging | 4380 | 12 |
| 3 | Brightbox | 3950 | 21 |
| 4 | Cobalt Supply | 4120 | 9 |
| 5 | Dunmore & Sons | ||
| 6 | Evergreen Pack | 4015 | 14 |
Common mistakes
Zeros used for "no data"
MIN skips blank cells, but a typed
0counts and becomes the minimum. Leave missing values empty, or useMINIFS(B2:B6,B2:B6,">0").MIN returns 0 with no numbers
If the range has no numbers at all (for example, they're stored as text), MIN shows 0 instead of an error. Check that the values are real numbers.
Mixing up MIN and MAX for caps
To cap a value at a limit, use MIN:
=MIN(B2,F1)never goes above F1. To set a floor, use MAX:=MAX(0,B2)never goes below 0.
FAQ
How do I find the smallest value that meets a condition?+
Use MINIFS: =MINIFS(B2:B20,C2:C20,"North") returns the lowest value for the North rows.
How do I find the second-lowest value?+
Use SMALL: =SMALL(B2:B6,2) returns the second smallest, SMALL(B2:B6,3) the third.
How do I return the name that goes with the lowest value?+
Look up the MIN: =XLOOKUP(MIN(B2:B6),B2:B6,A2:A6), or =INDEX(A2:A6,MATCH(MIN(B2:B6),B2:B6,0)) in older versions.
Lesson
Master MIN step by step: AVERAGE, MIN and MAX
Summarize a list with its average, smallest and largest values.