Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
MIN / MAX
AggregationThe smallest or largest number in a range — and a neat way to cap or floor a value.
Difficulty
1What is it?
MIN returns the smallest number in a range and MAX the largest, skipping blanks and text. Because dates are numbers underneath, MIN also gives you the earliest date and MAX the latest. A less obvious use is clamping a single value: =MIN(cap, x) never lets x exceed the cap, and =MAX(floor, x) never lets it drop below the floor — handy for tiered pricing, discount limits, or keeping a calculated quantity non-negative.
2What it looks like
MIN(number1, [number2], …)- number1
- The first number or range.
- [number2], …
- Optional more numbers or ranges.
3When you use it
- Largest invoice or smallest balance in a column.
- Earliest (MIN) or latest (MAX) date in a list.
- Cap or floor a value: =MIN(1000, commission) or =MAX(0, net).
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Pull the extreme value from B2:B5. Flip between MIN and MAX and edit the numbers.
=MAX(B2:B5)$5,400| A | B | C | |
|---|---|---|---|
| 1 | Item | Value | Result |
| 2 | Acme | $5,400 | |
| 3 | Globex | ||
| 4 | Initech | ||
| 5 | Umbrella |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Returns 0The range holds only text and blanks — nothing numeric.Fix: 0 is the documented result when there's no number; confirm the range has values.
Capped the wrong wayMIN and MAX are swapped when clamping.Fix: Use MIN to enforce an upper limit and MAX to enforce a lower one — it reads backwards at first.
Ignored a dateA date is stored as text, so MIN/MAX skips it.Fix: Make sure dates are real dates, not text that looks like one.
6Better functions & alternatives
- MINIFS / MAXIFS — The smallest or largest that also meets conditions.
- SMALL / LARGE — The 2nd, 3rd … nth smallest or largest, not just the extreme.
- MEDIAN — The middle value when the extremes aren't what you want.
Want MIN / MAX already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →