All versionsExamples need Excel 2021 or laterMath

ROUNDDOWN

AFRONDEN.NAAR.BENEDEN in Dutch Excel

Cuts a number off at a chosen digit position, toward zero, never adding anything: 7.9 becomes 7 and -7.9 becomes -7.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1ContractAmount paidCost per monthFull months
2Insurance€4,700.00€ 4.700,00€400.00€ 400,001111=ROUNDDOWN(B2 / C2, 0)=ROUNDDOWN(B2 / C2; 0)
3Rent€9,000.00€ 9.000,00€1,500.00€ 1.500,00
4Software licence€2,500.00€ 2.500,00€300.00€ 300,00
5Maintenance€1,250.00€ 1.250,00€500.00€ 500,00

Result: 11

=ROUNDDOWN(B2 / C2, 0)
=ROUNDDOWN(B2 / C2; 0)

What ROUNDDOWN does and when to use it

What it does: it cuts off every digit beyond the position you name and never rounds up, for example 7.9 becomes 7.

When to use it: when only complete units count: full months a prepayment covers, whole years of service, loyalty points per full 5 euros, or a contract that says amounts are truncated. Negative numbers move toward zero (-7.9 becomes -7); for the next lower number use INT.

How Excel evaluates it

Excel works in this order:

  • it evaluates number completely, including any division inside it
  • it sets the sign aside and works with the size of the number
  • num_digits marks the last digit that survives: everything to the right of it is removed, and nothing is added to the surviving digits, whatever the removed part was
  • the sign is put back

That order is why the function moves toward zero: 3.999 with 0 digits is 3, and -3.999 is -3. A number that already fits the position comes back unchanged.

Watch out: ROUNDDOWN moves toward zero, so -3.9 becomes -3, not -4. For the mathematically lower whole number in both directions, use INT.

Mental model

Counting full crates on a pallet: a crate that is half full is not counted, and a crate you owe (a negative one) is only counted when it is completely owed.

Syntax

Official ROUNDDOWN documentation
ROUNDDOWN(number, num_digits)
ROUNDDOWN(number; num_digits)
ArgumentTypeMeaning
numberRequiredThe value to cut off toward zero; a number, a cell or a calculation.
num_digitsRequiredDigits to keep: 0 for whole units, 2 for cents, -2 to cut to hundreds.

Return value:A number: rounded toward zero to num_digits decimal places

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

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Full months a prepayment covers

For the cut-off test at Veldhuis Installatie BV the prepaid expenses list shows, per contract, the amount paid in advance and the monthly cost. You want to know for how many complete months each prepayment covers the cost. A month that is only partly paid is not covered, so 11.75 months means 11.

ABCDE
1ContractAmount paidCost per monthFull months
2Insurance€4,700.00€ 4.700,00€400.00€ 400,001111=ROUNDDOWN(B2 / C2, 0)=ROUNDDOWN(B2 / C2; 0)
3Rent€9,000.00€ 9.000,00€1,500.00€ 1.500,00
4Software licence€2,500.00€ 2.500,00€300.00€ 300,00
5Maintenance€1,250.00€ 1.250,00€500.00€ 500,00
6
Formula cell (D2)Input

Formula in D2

=ROUNDDOWN(B2 / C2, 0)
=ROUNDDOWN(B2 / C2; 0)

Result: 1111

Step by step
  1. Excel evaluates the division first: B2 / C2 = 4700 / 400 = 11.75, the number of months if a month could be paid in part.
  2. num_digits is 0, so the units digit is the last one kept. The .75 is dropped and nothing is added: 11.
  3. Eleven months cost 11 x 400 = 4,4004.400. The remaining 300 does not pay a twelfth month in full, so that month is not covered; ROUNDUP would have claimed 12.

  4. Copy down:

    • D3: 9000 / 1500 = 6 exactly, nothing to drop: 6
    • D4: 2500 / 300 = 8.33..., so 8
    • D5: 1250 / 500 = 2.5, and the .5 is dropped, not rounded up: 2
  5. Compare each result with the months that have passed since the payment. A prepayment that covers fewer months than have passed means part of the cost should already be in the profit and loss account.
  6. Change B2 to 4800 and D2 becomes 12, because 4800 / 400 = 12 exactly.

Watch out: A blank or 0 in the Cost per month column gives #DIV/0!, and ROUNDDOWN passes the error on. Every contract needs a monthly cost.

More on this example: why it works, 1 variation

Why it works

A division answers 'how many times does this fit', and the fractional part is the month that is not fully paid. ROUNDDOWN with 0 digits discards exactly that part. ROUND would have reported 12 months for the insurance, one month that the prepayment does not cover.

Variations

=INT(B2 / C2)
=INT(B2 / C2)

The same answer for positive amounts. The two differ only when the division is negative, for example a refund of a prepayment.

Intermediate

Full threshold steps per journal line, credits included

In the XAF auditfile of Veldhuis Installatie BV a debit is positive and a credit is negative. For journal entry testing you count how many full multiples of the review threshold (2,5002.500) each line represents, to prioritise the lines.

A credit of -7,6007.600 counts as 3 steps, like a debit of 7,6007.600 does; a line that stays below one full step counts as 0. One formula, copied down, must handle both signs.

ABCDEF
1Entry no.AmountStepsReview threshold
225-0412€12,400.00€ 12.400,0044=ROUNDDOWN(B2 / $E$2, 0)=ROUNDDOWN(B2 / $E$2; 0)25002500
325-0413-€7,600.00€ -7.600,00
425-0420€2,500.00€ 2.500,00
525-0431-€2,499.99€ -2.499,99
625-0447€31,250.50€ 31.250,50
7
Formula cell (C2)Input

Formula in C2

=ROUNDDOWN(B2 / $E$2, 0)
=ROUNDDOWN(B2 / $E$2; 0)

Result: 44

Step by step
  1. Excel reads B2 (12400) and $E$2 (2500) and divides: 12400 / 2500 = 4.96 steps if steps could be partial.
  2. num_digits 0 keeps the units and drops .96: 4 full steps.
  3. C3 is a credit: -7600 / 2500 = -3.04. ROUNDDOWN moves toward zero, so -3.04 becomes -3, the same size as for the debit of 7,6007.600.
  4. INT would have given -4 here, counting a step the line never completed. ROUND would have given -3 here but 5 on the first row. Only ROUNDDOWN treats both directions the same.

  5. The other rows:

    • C4: 2500 / 2500 = 1 exactly, so 1
    • C5: -2499.99 / 2500 = -0.999996, which truncates to 0 (INT would say -1)
    • C6: 31250.5 / 2500 = 12.5002, so 12
  6. The column adds up to 4 - 3 + 1 + 0 + 12 = 14 steps. Without the $ signs, C3 would divide by E3, which is empty, and show #DIV/0!. Change E2 to 5000 and the steps become 2, -1, 0, 0 and 6.

Watch out: The XAF export often has separate Debit and Credit columns, both positive. Subtract first, for example =ROUNDDOWN((D2 - E2) / $H$1, 0) with the real columns, or every credit is counted as a debit.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

Toward zero is the right direction for debits and credits alike, because the question is symmetric: how many full steps of the threshold fit in the size of the amount, whichever way it was posted. That symmetry is what ROUNDDOWN offers and INT does not. The absolute reference keeps every copy of the formula reading the one cell that defines the threshold.

More to watch out for

  • An amount that arrives as text from the export still divides, because Excel converts text inside arithmetic, but a SUM of the amounts column skips it and the total no longer reconciles with the trial balance.

Variations

=TRUNC(B2 / $E$2)
=TRUNC(B2 / $E$2)

Identical results; TRUNC with one argument is ROUNDDOWN with 0 digits under a different name.

Advanced

Booked audit minutes as hours and minutes past 24 hours

The time-tracking export for the audit of Veldhuis Installatie BV gives the minutes each team member booked in week 41. The budget-versus-actual report must show them as hours and minutes, such as 25:23. A cell formatted as a time cannot be used, because Excel's time format wraps at 24 hours and shows 1:23 for 25 hours 23 minutes.

You build the text yourself:

  • whole hours from ROUNDDOWN
  • the remaining minutes from MOD
  • padding to two digits with TEXT
ABCDE
1Team memberWeekMinutesHours:minutes
2Sanne de Vries41414654657:45=ROUNDDOWN(C2 / 60, 0) & ":" & TEXT(MOD(C2, 60), "00")=ROUNDDOWN(C2 / 60; 0) & ":" & TEXT(MOD(C2; 60); "00")
3Tom Bakker4141605605
4Aisha Yilmaz41411,5231.523
5Priya Raman41415959
6Daan Visser41411,4401.440
7
Formula cell (D2)Input

Formula in D2

=ROUNDDOWN(C2 / 60, 0) & ":" & TEXT(MOD(C2, 60), "00")
=ROUNDDOWN(C2 / 60; 0) & ":" & TEXT(MOD(C2; 60); "00")

Result: 7:45

Step by step
  1. Excel evaluates the first part: C2 / 60 = 465 / 60 = 7.75 hours. ROUNDDOWN with 0 digits drops the .75 and returns 7, the whole hours.
  2. Inside the second part, MOD(C2, 60) gives the remainder after dividing by 60: 465 - 7 × 60 = 45 minutes. ROUNDDOWN took the whole part; MOD takes what ROUNDDOWN discarded, expressed in minutes.
  3. TEXT(45, "00") formats that remainder as text with at least two digits: 45 stays 45. The format only matters for small values, as the next rows show.
  4. The & operator joins three pieces: the number 7 (converted to text), a colon, and the text 45. The cell shows 7:45.
  5. D3: 605 / 60 = 10.083..., whole hours 10; MOD(605, 60) = 5; TEXT pads it to 05, so 10:05 and not 10:5. D4: 1523 / 60 = 25.38..., so 25; MOD gives 1523 - 1500 = 23; result 25:23, which a time format would have shown as 1:23.
  6. D5: 59 / 60 = 0.983..., whole hours 0; MOD gives 59; result 0:59. D6: 1440 / 60 = 24 exactly; MOD gives 0, padded to 00; result 24:00.

Watch out: A negative correction of -30 minutes silently shows 0:30. ROUNDDOWN(-0.5, 0) is 0 and MOD(-30, 60) is 30, because MOD takes the sign of its divisor. Handle corrections with ABS and a separate minus sign, or keep them out of this column.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

ROUNDDOWN and MOD split a number into a whole part and a remainder that add back up to the original: 7 hours plus 45 minutes is 465 minutes again. Because both are computed from the same cell, they cannot drift apart. TEXT is needed only for the padding; without it 605 minutes would read 10:5.

More to watch out for

  • The result is text. SUM over column D returns 0, and sorting puts 9:15 after 25:23. Keep the minutes column for arithmetic and use this column for display only.
  • If the minutes are themselves calculated, for example from decimal hours or from time values multiplied by 1440, floating point can leave 479.99999999999994 where 480 was meant, and ROUNDDOWN keeps 7 hours while TEXT rounds the remaining 59.99999999999994 minutes up to 60, so the cell shows 7:60 instead of 8:00. Wrap such a source in ROUND(..., 0) before splitting it.

Variations

=TEXT(C2 / 1440, "[h]:mm")
=TEXT(C2 / 1440; "[h]:mm")

Excel's elapsed-time format in square brackets does not wrap at 24 hours; 1523 minutes shows 25:23. Shorter, but the format code differs per Excel language and the result is still text.

=INT(C2 / 60) & ":" & TEXT(MOD(C2, 60), "00")
=INT(C2 / 60) & ":" & TEXT(MOD(C2; 60); "00")

INT gives the same hours for positive minute counts and is what most people write; the difference only appears with negative corrections.

MasterExcel 2021 or later

Materiality rounded down to whole thousands, never below zero

In the planning of several audits you calculate overall materiality as a percentage of a benchmark (revenue, or result for some entities). Policy says to round materiality down to whole thousands, so that it never exceeds the calculated amount: 241,250241.250 becomes 241,000241.000.

For a loss-making entity whose benchmark is a negative result the calculation gives a negative number, and materiality must then show 0 so that you notice the benchmark needs another choice. Negative num_digits does the cutting; the sign case has to be handled on purpose.

ABCDE
1EntityBenchmarkPercentageMateriality
2Veldhuis Installatie BV€48,250,000.00€ 48.250.000,000.5%0,5%€241,000.00€ 241.000,00=LET( benchmark, B2, pct, C2, calculated, benchmark * pct, rounded, ROUNDDOWN(calculated, -3), MAX(0, rounded) )=LET( benchmark; B2; pct; C2; calculated; benchmark * pct; rounded; ROUNDDOWN(calculated; -3); MAX(0; rounded) )
3Bakker Logistiek BV€12,830,000.00€ 12.830.000,001%1%
4De Vries Beheer BV€940,000.00€ 940.000,000.5%0,5%
5Visser Horeca BV-€310,000.00€ -310.000,005%5%
6Yilmaz Groep BV€7,040,000.00€ 7.040.000,000.5%0,5%
7
Formula cell (D2)Input

Formula in D2

=LET(
  benchmark, B2,
  pct, C2,
  calculated, benchmark * pct,
  rounded, ROUNDDOWN(calculated, -3),
  MAX(0, rounded)
)
=LET(
  benchmark; B2;
  pct; C2;
  calculated; benchmark * pct;
  rounded; ROUNDDOWN(calculated; -3);
  MAX(0; rounded)
)

Result: €241,000.00€ 241.000,00

Step by step
  1. LET names the inputs: benchmark is 48250000 from B2 and pct is 0.005 from C2. calculated multiplies them: 48250000 x 0.005 = 241250.
  2. rounded is ROUNDDOWN(241250, -3). A negative count moves the cut three places left of the decimal point: the hundreds, tens and units digits are dropped, so 241250 becomes 241000. Nothing is added, so materiality never exceeds the calculated amount.
  3. MAX(0, 241000) returns 241000, because the value is already positive. ROUND would give the same here, but for D4 it would turn 4,7004.700 into 5,0005.000, above the calculated amount.
  4. Copy down. D3: 12830000 x 0.01 = 128300, cut to thousands gives 128000. D4: 940000 x 0.005 = 4700, and ROUNDDOWN(4700, -3) = 4000.

  5. D5 is the loss-making entity: -310000 x 0.05 = -15500. ROUNDDOWN toward zero gives -15000, a negative materiality that is meaningless. MAX(0, -15000) replaces it with 0, which tells you to pick another benchmark.

    D6: 7040000 x 0.005 = 35200, cut to 35000.

  6. Change B2 to 48999999: calculated becomes 244999.995, and D2 drops to 244000, not 245000, because the cut never rounds up.

Watch out: The mirror-image mistake is ROUNDDOWN(calculated, 3), a positive 3: it keeps three decimals instead of cutting to thousands, returns 241250 unchanged, and the planning document shows a materiality that does not follow the policy.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

A negative num_digits is a normal position instruction: -3 means 'the thousands digit is the last one that survives', and because ROUNDDOWN never adds, materiality can never exceed the calculated amount. The sign case is separate: toward zero turns a negative benchmark into a small negative materiality rather than zero, so the floor has to be explicit, and MAX(0, x) is the clearest way to write it.

Do not use this construction when the policy rounds to a step that is not a power of ten (steps of 25,00025.000, say): then INT(calculated / 25000) * 25000 or FLOOR.MATH(calculated, 25000) is the alternative, with the trade-off that INT floors negatives further down, so the MAX still matters.

More to watch out for

  • If the benchmark is itself a formula result, it can arrive as 34999.999999999993 where 35000 was meant, and the cut can then give 34000. Round the product with ROUND(..., 2) before cutting when it comes from arithmetic.
  • A text value in the Benchmark column, such as an amount imported with a currency symbol, gives #VALUE! and MAX passes the error through.

Variations

=MAX(0, ROUNDDOWN(B2 * C2, -3))
=MAX(0; ROUNDDOWN(B2 * C2; -3))

The same logic without LET; fine for a one-line sheet, harder to review when the rounding step and the floor have to be explained to the engagement partner.

=MAX(0, INT(B2 * C2 / 1000) * 1000)
=MAX(0; INT(B2 * C2 / 1000) * 1000)

Divide by the step, floor, multiply back: works for any step, not only powers of ten. For the loss-making entity INT gives -16000, so the MAX is still required.

Common mistakes

SymptomCauseFix
-3.9 becomes -3, not -4A negative balance of -3.9 comes back as -3, and you expected -4.ROUNDDOWN works toward zero by design. INT would give -4.
7 instead of 8ROUNDDOWN((0.1 + 0.7) * 10, 0) can return 7, although the calculation is 8.Binary floating point can store that product as 7.999999999999999, and truncation has no tolerance. Wrap the input in ROUND(..., 10) first when it comes from arithmetic on decimals.
Total lower than the customer'sInvoice lines truncated with ROUNDDOWN(x, 2) add up to less than the customer's own calculation.Money rounds to the nearest cent unless a contract says otherwise. Use ROUND(x, 2) and keep ROUNDDOWN for quantities.

Used in combinations

  • Systematic sample of invoices with a fixed startSelect every k-th invoice from a population: the interval comes from ROUNDDOWN and ROWS, SEQUENCE counts the picks from a fixed start typed in a cell, and INDEX lists the selected rows.Excel 2021 or later