Why does =MIN(A2:A50) return 0 when all your sales figures are $12,400 or higher? Why does it ignore a cell with 'N/A' but crash on #VALUE! — even though both look like errors? Why does =MIN(B2:B20) pull in a date from 1900 when you only wanted dollar amounts?
Quick Answer
The MIN function returns the smallest numeric value in a range — but it silently skips text, logical values (TRUE/FALSE), and empty cells. It does not skip error values (like #N/A or #DIV/0!), and it treats dates and times as numbers (so 1 Jan 1900 = 1). If your range contains even one error, MIN returns that error — no warning, no prompt.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| =MIN(range) | Type =MIN(A2:A15) and press Enter | Clean numeric data with no errors | Fails completely if any cell contains #N/A, #REF!, etc. |
| =AGGREGATE(5,6,range) | Type =AGGREGATE(5,6,A2:A15) — 5 = MIN, 6 = ignore errors | Data with mixed errors and numbers | Not available in Excel 2003 or earlier; harder to remember syntax |
| Array formula with IF + ISNUMBER | =MIN(IF(ISNUMBER(A2:A15),A2:A15)) — then press Ctrl+Shift+Enter (or Enter in Excel 365) | Selective filtering (e.g., only positive numbers, or values > 0) | Requires array entry in older Excel; slow on 10K+ rows |
| Helper column + MIN | In B2: =IF(ISNUMBER(A2),A2,"") → drag down → =MIN(B2:B15) | Auditable workflows or shared files where others edit | Adds clutter; breaks if helper column is accidentally deleted |
Method 1 Deep Dive
Let’s say you’re tracking Q1 sales across 7 regional managers. Your raw data lives in A2:A8:
| A |
|---|
| $82,500 |
| $64,120 |
| #N/A |
| $91,300 |
| "Pending" |
| $77,850 |
| 0 |
Type =MIN(A2:A8) in cell C2. You’ll get #N/A — not $64,120, not $0. That’s because MIN sees the #N/A and bails out immediately. It doesn’t care that six other cells are clean numbers. (Trust me, I learned this the hard way during a board presentation.)
Now try =AGGREGATE(5,6,A2:A8) in C3. Result: $64,120. The '5' tells Excel you want MIN. The '6' means “ignore error values.” This is the safest go-to for real-world datasets — especially when pulling from external systems or dashboards fed by Power Query.
Method 2 Deep Dive
Say you manage vendor contracts and need the earliest *valid* start date — but some rows contain "TBD", "—", or blank cells. Your dates sit in column D2:D12:
| D |
|---|
| 2024-03-15 |
| 2024-05-22 |
| "TBD" |
| 2024-01-08 |
| #VALUE! |
| 2024-07-30 |
| 2023-11-12 |
| "N/A" |
| 2024-04-05 |
| 2024-06-17 |
You want the earliest date — but only among actual dates. =MIN(D2:D12) fails with #VALUE!. =AGGREGATE(5,6,D2:D12) still fails, because #VALUE! is an error — but now you’ve got text too. So we combine logic: =MIN(IF(ISNUMBER(D2:D12),D2:D12)).
In Excel 365 or 2021, just type it and press Enter. In Excel 2019 or earlier? Type it, then hold Ctrl+Shift and press Enter — you’ll see braces { } appear around the formula. That’s your signal it’s working as an array.
Result: 2023-11-12 (serial number 45242). Bonus tip: ISNUMBER catches dates (they’re numbers), but rejects "TBD", blanks, and text — exactly what we need. And yes, it ignores #VALUE! automatically because ISNUMBER returns FALSE for it, so that cell gets excluded from the MIN calculation.
Cheat Sheet
| Task | Formula | Shortcut / Notes |
|---|---|---|
| Basic MIN (no errors) | =MIN(A2:A100) | Fastest. Use only when you’ve cleaned data first. |
| MIN ignoring errors | =AGGREGATE(5,6,A2:A100) | Alt + M, U, 5 → opens AGGREGATE wizard (Windows only) |
| MIN of positive numbers only | =MIN(IF(A2:A100>0,A2:A100)) | Ctrl+Shift+Enter in legacy Excel; Enter in 365 |
| MIN excluding zeros | =MINIFS(A2:A100,A2:A100,">0") | MINIFS requires Excel 2016+. Zeroes won’t pollute your result. |
| Confirm numeric-only range | =COUNT(A2:A100)=COUNTA(A2:A100) | Returns TRUE only if every non-blank cell is numeric. |
