MIN
MIN in Dutch Excel
Returns the smallest number among its arguments, ignoring blanks and text in ranges.
Last updated: 13 October 2026
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Period | Closing balance | Lowest balance | |
| 2 | 11 | €48,250.00€ 48.250,00 | €12,780.50€ 12.780,50=MIN(B2:B7)=MIN(B2:B7) | |
| 3 | 22 | €31,400.00€ 31.400,00 | ||
| 4 | 33 | €12,780.50€ 12.780,50 | ||
| 5 | 44 | €27,900.00€ 27.900,00 | ||
| 6 | 55 | €41,350.00€ 41.350,00 | ||
| 7 | 66 | €19,600.00€ 19.600,00 |
Swipe sideways to see all columns
Result: €12,780.50€ 12.780,50
=MIN(B2:B7)
=MIN(B2:B7)
What MIN does and when to use it
What it does: it returns the smallest number in the values you give it, for example the lowest quote from a supplier.
When to use it: to find the lowest price, the earliest date or the shortest time. It also caps a value: MIN(amount, limit) never goes above the limit, which suits expense caps and capped bonuses. For the lowest value under a condition, use MINIFS.
How Excel evaluates it
Excel collects the values from every argument. From a range or a cell reference it takes only numbers; blanks, text and TRUE or FALSE are left out. A value typed directly into the formula is converted if possible.
Excel then keeps the smallest value. If nothing numeric was found at all, the result is 0, and if any cell holds an error value, MIN returns that error.
MIN always returns one number: given a whole column and a cap, it returns the smallest of all of them, not a capped column.
Watch out: When zeros in the data mean 'no value' rather than a real zero, plain MIN returns 0. Use MINIFS with ">0" instead.
Mental model
A low doorway: whatever walks through comes out no taller than the frame. MIN(x, cap) is the doorway; x is whoever walks through.
Terms used on this page: reference, error value, argument, range, cell
MIN(number1,[number2], ...)
MIN(number1;[number2]; ...)
| Argument | Type | Meaning |
|---|---|---|
number1 | Required | The first number, cell or range to compare. |
number2 | Optional | More numbers, cells or ranges; a fixed number here acts as a ceiling. |
Return value:A number: the smallest value in the arguments
Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).
Worked examples
Beginner
Lowest month-end bank balance
In the audit of Veldhuis Installatie BV you have the month-end balances of grootboekrekening 1100 Bank for periods 1 to 6, taken from the saldibalans. To judge liquidity during the year you want the lowest balance in one cell, so it updates when a period is corrected.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Period | Closing balance | Lowest balance | ||
| 2 | 11 | €48,250.00€ 48.250,00 | €12,780.50€ 12.780,50=MIN(B2:B7)=MIN(B2:B7) | ||
| 3 | 22 | €31,400.00€ 31.400,00 | |||
| 4 | 33 | €12,780.50€ 12.780,50 | |||
| 5 | 44 | €27,900.00€ 27.900,00 | |||
| 6 | 55 | €41,350.00€ 41.350,00 | |||
| 7 | 66 | €19,600.00€ 19.600,00 | |||
| 8 |
Swipe sideways to see all columns
Formula in D2
=MIN(B2:B7)
=MIN(B2:B7)
Result: €12,780.50€ 12.780,50
Step by step
- Excel reads
B2:B7and finds six numbers: 48250, 31400, 12780.50, 27900, 41350 and 19600. - It starts with 48250 as the smallest so far. 31400 is smaller, so that takes over. 12780.50 is smaller still and takes over in turn.
- 27900, 41350 and 19600 are each compared with 12780.50. None is smaller.
- MIN returns 12780.50, the balance at the end of period 3.
- If period 3 is corrected to 15200 after a late bank posting,
D2becomes 15200 on its own, because 15200 is still below 19600.
Watch out: If periods 7 to 12 are filled in as 0 for months not yet closed, the lowest balance becomes 0. Leave future periods blank; MIN skips blanks.
More on this example: why it works
Why it works
MIN compares every value with the smallest one found so far, so the order of the periods does not matter. You get the balance, not the period; MATCH on the result tells you which month it was.
Intermediate
Insured part of receivables per debtor
In the receivables aging list, the credit insurer covers each debtor up to 25,00025.000. You want to know how much of each open balance is insured, to size the uninsured exposure when you assess the provision.
A debtor that paid in full has a blank balance and must show 0. The formula in C2 is copied down.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Relation | Open balance | Insured | Limit per debtor | 2500025000 | |
| 2 | D1001 | €38,400.00€ 38.400,00 | €25,000.00€ 25.000,00=IF(B2 = "", 0, MIN(B2, $E$1))=IF(B2 = ""; 0; MIN(B2; $E$1)) | |||
| 3 | D1002 | €17,250.50€ 17.250,50 | ||||
| 4 | D1003 | |||||
| 5 | D1004 | €61,200.00€ 61.200,00 | ||||
| 6 | D1005 | €9,800.00€ 9.800,00 | ||||
| 7 |
Swipe sideways to see all columns
Formula in C2
=IF(B2 = "",0, MIN(B2, $E$1))
=IF(B2 = "";0; MIN(B2; $E$1))
Result: €25,000.00€ 25.000,00
Step by step
- Excel evaluates the test: is
B2empty?B2holds 38400, so the test is FALSE and IF moves to its third argument. - MIN compares 38400 with
$E$1, 25000, and returns the smaller: 25000. Debtor D1001 is insured up to the limit. - Copied down:
C3gives MIN(17250.50, 25000) = 17250.50, below the limit, so fully insured.C5gives MIN(61200, 25000) = 25000.C6gives MIN(9800, 25000) = 9800. C4is debtor D1003, whoseB4is blank. The testB4= "" is TRUE and IF returns 0.- Without the IF,
C4would be MIN(B4, $E$1). MIN skips the blank cell, finds only 25000 and returns 25000: insured cover for a balance that does not exist. - The column adds up to 25000 + 17250.50 + 0 + 25000 + 9800 = 77050.50. Without the blank test it would be 102050.50.
Watch out: A balance that arrives as text, such as '38400.00' from a badly exported aging list, is skipped by MIN in the same way as a blank, and the row shows the full limit. The IF does not catch it, because the cell is not empty.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
MIN with the limit as a second argument returns the balance when it is below the limit and the limit when it is above, which is how the insurance cover works. Because MIN skips blanks instead of treating them as 0, the empty balance needs its own test, and IF handles it before MIN ever sees the cell. The $ signs keep every row pointed at the one limit cell.
More to watch out for
- Without the $ signs,
C3would compare withE2, an empty cell that MIN skips, and show D1002 and D1004 as fully insured, including the 61200 of D1004.
Variations
=IF(ISNUMBER(B2),MIN(B2, $E$1), 0)
=IF(ISNUMBER(B2);MIN(B2; $E$1); 0)
Tests for a real number instead of a blank, so text balances also show 0 and stand out for follow-up.
AdvancedExcel 2021 or later (This example needs Excel 2021 or later)
Entry with the lowest debit on an account
For journal entry testing you have the XAF journal lines of Veldhuis Installatie BV. Credit lines carry a 0 in the Debit column. For the account in G1 you want the entry number with the lowest real debit, and a clear message if the account has no debit postings at all.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Account | Entry no. | Debit | Account | 45004500 | |||
| 2 | 45004500 | 25-0101 | €1,250.00€ 1.250,00 | Entry with lowest debit | 25-0212=XLOOKUP(1, (A2:A9 = G1) * (C2:C9 > 0) * (C2:C9 = MINIFS(C2:C9, A2:A9, G1, C2:C9, ">0")), B2:B9, "No debit postings")=XLOOKUP(1; (A2:A9 = G1) * (C2:C9 > 0) * (C2:C9 = MINIFS(C2:C9; A2:A9; G1; C2:C9; ">0")); B2:B9; "No debit postings") | |||
| 3 | 45004500 | 25-0145 | €0.00€ 0,00 | |||||
| 4 | 40004000 | 25-0150 | €3,400.00€ 3.400,00 | |||||
| 5 | 45004500 | 25-0212 | €980.40€ 980,40 | |||||
| 6 | 80008000 | 25-0230 | €0.00€ 0,00 | |||||
| 7 | 45004500 | 25-0301 | €1,875.00€ 1.875,00 | |||||
| 8 | 40004000 | 25-0302 | €3,150.00€ 3.150,00 | |||||
| 9 | 45004500 | 25-0377 | €1,310.00€ 1.310,00 | |||||
| 10 |
Swipe sideways to see all columns
Formula in G2
=XLOOKUP(1,(A2:A9 = G1) * (C2:C9 > 0) * (C2:C9 = MINIFS(C2:C9, A2:A9, G1, C2:C9, ">0")), B2:B9, "No debit postings")
=XLOOKUP(1;(A2:A9 = G1) * (C2:C9 > 0) * (C2:C9 = MINIFS(C2:C9; A2:A9; G1; C2:C9; ">0")); B2:B9; "No debit postings")
Result: 25-0212
Step by step
- Excel evaluates MINIFS first. It keeps rows where A equals 4500 and C is greater than 0: rows 2, 5, 7 and 9, with 1250, 980.40, 1875 and 1310. Row 3, the 0 of entry
25-0145, fails">0". The smallest is 980.40. - Without
">0", MINIFS would return 0 from row 3 and name entry25-0145, a credit line. - Next the three tests become arrays of 1 and 0 and are multiplied. Account equals 4500: 1, 1, 0, 1, 0, 1, 0, 1. Debit above 0: 1, 0, 1, 1, 0, 1, 1, 1. Debit equals 980.40: 0, 0, 0, 1, 0, 0, 0, 0. The product is 0, 0, 0, 1, 0, 0, 0, 0.
- XLOOKUP looks for 1 in that array and finds it in the fourth position. It returns the fourth cell of
B2:B9, which isB5:25-0212. - Change
G1to 4000: MINIFS finds 3400 and 3150, returns 3150, and XLOOKUP returns25-0302. - Change
G1to 8000: its only line has a debit of 0. MINIFS finds no row and returns 0. The account test matches row 6, but the > 0 test zeroes it out, no 1 remains, and XLOOKUP returns No debit postings.
Watch out: Looking up the amount alone, XLOOKUP(MINIFS(...), C2:C9, B2:B9), finds the first row with that amount on any account. If an entry on account 4000 higher in the list also had a debit of 980.40, it would name the wrong entry without any error.
More on this example: why it works, 2 more pitfalls, 2 variations
Why it works
MINIFS returns an amount, not a position, so the amount has to be found again among the rows of the same account. Multiplying the tests is an AND over arrays, and XLOOKUP on 1 finds the first row where all of them hold. The extra > 0 test is there because MINIFS returns 0 when nothing qualifies, and that 0 would otherwise match a credit line.
More to watch out for
- MINIFS needs Excel 2019 or later. In older versions the formula shows #NAME?.
- Two entries with the same lowest debit: XLOOKUP returns the first one in the list. If ties matter for your testing, use FILTER to return all of them.
Variations
=MINIFS(C2:C9,A2:A9, G1, C2:C9, ">0")
=MINIFS(C2:C9;A2:A9; G1; C2:C9; ">0")
The lowest real debit on its own, 980.40, when you do not need the entry number.
=FILTER(B2:B9,(A2:A9 = G1) * (C2:C9 > 0) * (C2:C9 = MINIFS(C2:C9, A2:A9, G1, C2:C9, ">0")), "No debit postings")
=FILTER(B2:B9;(A2:A9 = G1) * (C2:C9 > 0) * (C2:C9 = MINIFS(C2:C9; A2:A9; G1; C2:C9; ">0")); "No debit postings")
Returns every entry tied for the lowest debit, spilled down, instead of only the first.
MasterExcel 2024 or later (This example needs Excel 2024 or later)
Follow-up threshold clamped per account
For each account in the saldibalans, the audit plan sets a threshold above which a difference is followed up. The threshold is:
- 0.5% of the balance
- at least 1,0001.000
- at most 10,00010.000
The whole threshold column should come from one formula in C2 that spills down, so an account added to the list cannot be missed by a formula that was not copied.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Account | Balance | Threshold | Rate | 0.0050,005 | |
| 2 | 11001100 | €842,300.00€ 842.300,00 | €4,211.50€ 4.211,50=LET( balances, B2:B7, rate, E1, minThreshold, E2, maxThreshold, E3, MAP(balances, LAMBDA(s, MAX(minThreshold, MIN(s * rate, maxThreshold)))) )=LET( balances; B2:B7; rate; E1; minThreshold; E2; maxThreshold; E3; MAP(balances; LAMBDA(s; MAX(minThreshold; MIN(s * rate; maxThreshold)))) ) | Minimum | 10001000 | |
| 3 | 13001300 | €1,263,500.00€ 1.263.500,00 | €6,317.50€ 6.317,50 | Maximum | 1000010000 | |
| 4 | 16001600 | €415,800.00€ 415.800,00 | €2,079.00€ 2.079,00 | |||
| 5 | 18001800 | €38,900.00€ 38.900,00 | €1,000.00€ 1.000,00 | |||
| 6 | 40004000 | €2,150,000.00€ 2.150.000,00 | €10,000.00€ 10.000,00 | |||
| 7 | 45004500 | €96,400.00€ 96.400,00 | €1,000.00€ 1.000,00 | |||
| 8 |
Swipe sideways to see all columns
Formula in C2
=LET( balances,B2:B7, rate, E1, minThreshold, E2, maxThreshold, E3, MAP(balances, LAMBDA(s, MAX(minThreshold, MIN(s * rate, maxThreshold)))) )
=LET( balances;B2:B7; rate; E1; minThreshold; E2; maxThreshold; E3; MAP(balances; LAMBDA(s; MAX(minThreshold; MIN(s * rate; maxThreshold)))) )
Result: €4,211.50 / €6,317.50 / €2,079.00 / €1,000.00 / €10,000.00 / €1,000.00€ 4.211,50 / € 6.317,50 / € 2.079,00 / € 1.000,00 / € 10.000,00 / € 1.000,00
Step by step
- LET names the inputs: balances is
B2:B7, rate is 0.005, minThreshold is 1000 and maxThreshold is 10000. - MAP walks through balances one value at a time and hands each value to the LAMBDA as s. Inside, MIN and MAX see one number and one limit each time, never the whole column.
- Account 4000: 2150000 × 0.005 = 10750. MIN(10750, 10000) = 10000, capped. MAX(1000, 10000) = 10000.
- Account 1800: 38900 × 0.005 = 194.50. MIN(194.50, 10000) = 194.50, then MAX(1000, 194.50) = 1000: the floor lifts it to the minimum. Account 4500: 96400 × 0.005 = 482, lifted to 1000 the same way.
- Account 1100: 842300 × 0.005 = 4211.50, account 1300: 1263500 × 0.005 = 6317.50, account 1600: 415800 × 0.005 = 2079. All three lie between the limits and pass through unchanged.
- MAP collects the six results and spills them from
C2toC7: 4211.50, 6317.50, 2079, 1000, 10000 and 1000, a total of 24608. ChangeE3to 12000 and account 4000 becomes 10750.
Watch out: The mirror-image mistake is =MAX(E2, MIN(B2:B7 * E1, E3)). MIN takes the smallest of all six thresholds and the maximum, 194.50, MAX lifts it to 1000, and the formula returns one 1000 instead of a column. It looks like a valid threshold.
More on this example: why it works, 2 more pitfalls, 2 variations
Why it works
MAX(low, MIN(x, high)) clamps one number: MIN takes it down to the ceiling if it is above, MAX lifts it to the floor if it is below. Because MIN and MAX aggregate everything they are given, the clamp has to be applied to each value separately, and MAP with a LAMBDA does exactly that inside a single spilling formula.
Do not use this construction if the workbook is opened in Excel 2019 or earlier, where MAP and LAMBDA do not exist. There the per-row formula =MAX($E$2, MIN(B2 * $E$1, $E$3)) copied down is the alternative, clearer to most readers, but a copy is quickly forgotten when the list grows.
More to watch out for
- Swapping the nesting to MIN(maxThreshold, MAX(s * rate, minThreshold)) gives the same results only while the floor is below the cap. If someone sets the minimum above the maximum, the two orders disagree, so validate that
E2<E3. - An account with a blank balance gets s = 0, a threshold of 0 × 0.005 = 0, and is lifted to the minimum of 1000. Decide whether empty accounts need a threshold at all and filter them out if not.
Variations
=MAX($E$2,MIN(B2 * $E$1, $E$3))
=MAX($E$2;MIN(B2 * $E$1; $E$3))
The per-row clamp, copied down from C2: works in every version.
=IF(B2:B7 * E1 > E3,E3, IF(B2:B7 * E1 < E2, E2, B2:B7 * E1))
=IF(B2:B7 * E1 > E3;E3; IF(B2:B7 * E1 < E2; E2; B2:B7 * E1))
A spilling clamp without MAP: IF compares element by element, where MIN and MAX would aggregate. Longer, and the product is calculated three times.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| Lowest quote comes out as 0 | The lowest quote comes out as 0, because suppliers who did not quote were entered as 0. | Leave missing quotes blank, or use MINIFS(C2:C9, C2:C9, ">0") to skip the zeros. |
| Full cap returned for an empty claim | MIN(B2, $E$1) returns the full cap for an empty claim, because the blank B2 is skipped and only the cap is left. | Test for a blank first: IF(B2 = "", 0, MIN(B2, $E$1)), as in the Intermediate example. |
| One number instead of a column of capped values | Writing MIN(B2:B7 * E1, E3) to cap a whole column at once, and getting a single number back. | MIN aggregates. Cap per row with a formula copied down, or apply MIN to each element with MAP and LAMBDA, as in the Master example. |
Used in combinations
- Gap test on invoice numbers with FILTER, SEQUENCE and XMATCHFind the missing numbers in a series of sales invoices: SEQUENCE builds every number from the lowest to the highest, XMATCH looks each one up in the ledger, ISNA marks the misses and FILTER lists them, with a message when there are no gaps.Excel 2021 or later (This combination needs Excel 2021 or later)