All versionsExamples need Excel 2021 or laterMath

ROUNDUP

AFRONDEN.NAAR.BOVEN in Dutch Excel

Rounds a number away from zero to a chosen number of digits, so any leftover fraction counts as a full extra unit.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABCD
1JournalLinesIntervalSample size
2VRK1,2501.25040403232=ROUNDUP(B2 / C2, 0)=ROUNDUP(B2 / C2; 0)
3INK9609604040
4BNK2,2102.2105050
5MEM87871010

Result: 32

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

What ROUNDUP does and when to use it

What it does: it rounds away from zero as soon as anything is left over, for example 2.01 becomes 3.

When to use it: when a part of a unit still costs a whole one: boxes for a quantity, pallets for a shipment, hours billed in whole quarters, licenses for a headcount. It also suits prudent estimates. For steps that are not a power of ten, such as crates of 12, CEILING.MATH is shorter.

How Excel evaluates it

Excel first evaluates number in full, including any division inside it. It then finds the digit position given by num_digits and looks at everything to the right of it.

If all of that is zero, the number is returned as it is: ROUNDUP(3, 0) is 3, not 4. If anything at all is non-zero, even 0.0001, the digit at the chosen position moves one step away from zero and everything after it is dropped.

The sign is left alone and applied at the end, which is why -3.14 with one digit becomes -3.2 and not -3.1.

Watch out: ROUNDUP rounds away from zero, not up: a negative number moves further from zero, so -2.01 becomes -3.

Mental model

A taxi meter that ticks at the start of every kilometer: driving 4.1 km costs 5 ticks, and driving exactly 4.0 km costs 4.

Syntax

Official ROUNDUP documentation
ROUNDUP(number, num_digits)
ROUNDUP(number; num_digits)
ArgumentTypeMeaning
numberRequiredThe value to round away from zero; a number, a cell or a calculation.
num_digitsRequiredDigits to keep: 0 for whole units, 2 for cents, -2 for hundreds.

Return value:A number: rounded away from 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

How many lines to select per journal

For a journal entry test on the XAF auditfile you select every 40th line (systematic selection). The sales journal VRK has 1,2501.250 lines, which is 31.25 selections, and you cannot test a quarter of a line.

Rounding down would leave the end of the population uncovered, so you plan 32. You want a column that turns the number of lines and the interval of each journal into the sample size.

ABCDE
1JournalLinesIntervalSample size
2VRK1,2501.25040403232=ROUNDUP(B2 / C2, 0)=ROUNDUP(B2 / C2; 0)
3INK9609604040
4BNK2,2102.2105050
5MEM87871010
6
Formula cell (D2)Input

Formula in D2

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

Result: 3232

Step by step
  1. Excel evaluates the division first: B2 / C2 = 1250 / 40 = 31.25, the number of selections if a line could be split.
  2. num_digits is 0, so the units digit is the last one kept. Everything after it (.25) is not zero.
  3. Because a remainder exists, the units digit moves one step away from zero: 31 becomes 32. The sample for VRK is 32 lines.
  4. Copy down. D3: 960 / 40 = 24 exactly, nothing left over, so INK stays at 24. ROUNDUP does not add a line when the division is clean.
  5. D4: 2210 / 50 = 44.2, so BNK gives 45. D5: 87 / 10 = 8.7, so MEM gives 9.
  6. Change B2 to 1240 and D2 becomes 31, because 1240 / 40 = 31 with no remainder.

Watch out: If C2 is empty or 0 the division gives #DIV/0!, and ROUNDUP passes the error on. Every journal needs an interval.

More on this example: why it works, 1 variation

Why it works

The division gives the exact number of selections, and the question 'is there any fraction' is exactly what ROUNDUP with 0 digits answers. ROUND would give 31 for VRK, and INT would too, so the last part of the population would fall outside the sample. Only ROUNDUP treats a fraction as a reason to add a whole selection.

Variations

=CEILING.MATH(B2 / C2, 1)
=CEILING.MATH(B2 / C2; 1)

The same answer, with the rounding step written out as a significance of 1. Prefer CEILING.MATH when the step is not 1, for example when you select in batches.

Intermediate

Round misstatements to whole thousands, prudently

In the schedule of uncorrected misstatements you list the effect of each finding on the result of Veldhuis Installatie BV.

For the summary memo you report whole thousands of euros, and prudently: a finding that lowers the result (negative) is rounded away from zero so its effect is never understated, while a finding that raises the result (positive) is rounded toward zero so you do not take credit for more than there is. One formula copied down must apply both rules.

ABCDE
1Entry no.AccountEffect on resultReported
225-041245004500-€4,260.00€ -4.260,00-€5,000.00€ -5.000,00=IF(C2 < 0, ROUNDUP(C2, -3), ROUNDDOWN(C2, -3))=IF(C2 < 0; ROUNDUP(C2; -3); ROUNDDOWN(C2; -3))
325-051880008000€3,180.00€ 3.180,00
425-073340004000-€1,000.00€ -1.000,00
525-080113001300€750.00€ 750,00
625-090245004500-€400.00€ -400,00
7
Formula cell (D2)Input

Formula in D2

=IF(C2 < 0, ROUNDUP(C2, -3), ROUNDDOWN(C2, -3))
=IF(C2 < 0; ROUNDUP(C2; -3); ROUNDDOWN(C2; -3))

Result: -€5,000.00€ -5.000,00

Step by step
  1. IF tests C2 < 0. C2 is -4260, so the test is TRUE and Excel evaluates only the second argument, the ROUNDUP branch.
  2. ROUNDUP(-4260, -3) stops at the thousands digit. What is to the right of it (260) is not zero, so the digit moves one step away from zero: -4000 becomes -5000. The cost finding is reported as -5,0005.000, never less than it is.
  3. D3: 3180 is not below zero, so the ROUNDDOWN branch runs and moves toward zero: 3000. The revenue finding is reported as 3,0003.000 and the 180 is left out.

  4. The other rows:

    • D4: -1000 is negative, and ROUNDUP(-1000, -3) finds nothing right of the thousands digit, so it stays -1000
    • D5: 750 is positive, ROUNDDOWN gives 0
    • D6: -400 is negative, ROUNDUP gives -1000
  5. Without the IF, ROUNDUP alone would turn 3180 into 4000 and 750 into 1000: it takes credit for upside that is not there. That is the reason the positive case is separated.
  6. The reported column totals -5000 + 3000 - 1000 + 0 - 1000 = -4000. The exact effects total -4260 + 3180 - 1000 + 750 - 400 = -1730, so the prudent rounding adds 2,2702.270 of headroom to the net effect.

Watch out: The rule depends on the sign convention of the schedule. If positive means 'costs too low' instead of 'result higher', the formula rounds each finding the wrong way and every figure still looks tidy. Check the header of the schedule first.

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

Why it works

ROUNDUP and ROUNDDOWN are mirror images around zero, so pairing them inside an IF gives 'round costs away from zero, round upside toward zero'. That is a prudence policy, not an accident, and the sign test is what puts each finding on the right side of it. A negative num_digits of -3 makes both functions work on thousands without any division.

More to watch out for

  • The total of the rounded lines (-4,0004.000) is not the rounded total of the exact lines. Report the net effect from the exact column and keep the rounded column for the line items, or the memo and the schedule will disagree.

Variations

=ROUNDUP(C2, -3)
=ROUNDUP(C2; -3)

The bare version, away from zero in both directions: -4260 becomes -5000 and 3180 becomes 4000. Use it when the policy is to round every finding away from zero, for example for a list of exceptions.

Advanced

Fieldwork days from tests with different throughput

You plan the fieldwork days for the year-end audit. Each test has a number of items and an expected throughput in items per day.

The same auditor can do several tests on one day, so you calculate the days from the total of the day fractions, not per test: a plan that uses 2.6 days needs 3 days booked. The engagement budget also holds a minimum of 1 day, even if every test were dropped.

ABCDEF
1TestItemsItems per dayDay fractions
2Journal entry testing72724848
3Cut-off testing45456060Days to book
4Invoice matching181812012033=MAX(1, ROUNDUP(SUMPRODUCT(B2:B5 / C2:C5), 0))=MAX(1; ROUNDUP(SUMPRODUCT(B2:B5 / C2:C5); 0))
5Confirmation follow-up884040
6
Formula cell (E4)Input

Formula in E4

=MAX(1, ROUNDUP(SUMPRODUCT(B2:B5 / C2:C5), 0))
=MAX(1; ROUNDUP(SUMPRODUCT(B2:B5 / C2:C5); 0))

Result: 33

Step by step
  1. The innermost step is the array division B2:B5 / C2:C5, one fraction per test: 72 / 48 = 1.5, 45 / 60 = 0.75, 18 / 120 = 0.15 and 8 / 40 = 0.2.
  2. SUMPRODUCT adds the four fractions: 1.5 + 0.75 + 0.15 + 0.2 = 2.6 days of work. It is used here as a SUM that accepts an array without Ctrl+Shift+Enter in older Excel.
  3. ROUNDUP(2.6, 0): there is a remainder of 0.6, so the units digit goes from 2 to 3. Three days.
  4. MAX(1, 3) compares the budget minimum with the calculated need and returns the larger: 3. The minimum only bites when the rounded result is 0, which happens when no items are planned.
  5. Rounding per test would have given 2 + 1 + 1 + 1 = 5 days, two more than the plan needs, because each test's spare time is wasted. Adding fractions first and rounding once is what makes the answer 3.
  6. Clear all four item counts and the sum is 0, ROUNDUP gives 0, and MAX lifts it to 1: the budget minimum. Set B2 to 96 and the fractions sum to 2 + 0.75 + 0.15 + 0.2 = 3.1, so 4 days.

Watch out: If an items-per-day value is 0 or blank, that line's division is #DIV/0! and the whole result is #DIV/0!.

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

Why it works

Fractions of a day from different tests can share one physical day, so the correct number is the rounded-up total, not the total of rounded-up lines. ROUNDUP is applied exactly once, at the level where the constraint lives: whole days booked. MAX(1, ...) is the standard way to put a floor under a result without an IF.

Watch out for

  • If an items-per-day value is 0 or blank, that line's division is #DIV/0! and the whole result is #DIV/0!.

    Silent version: if someone swaps the two numbers on the journal entry line (48 items, 72 per day), that line becomes 48 / 72 = 0.667, the total drops to 1.767 and the formula returns 2, which looks plausible but is one day short.

  • Some tests need a specialist who cannot share a day with the other work (for example an IT auditor on the journal entry test). Then rounding per test, 5 here, is the right answer, and this formula under-books.

Variations

=SUMPRODUCT(ROUNDUP(B2:B5 / C2:C5, 0))
=SUMPRODUCT(ROUNDUP(B2:B5 / C2:C5; 0))

Per-test rounding, 5 days: use it when tests cannot share a day.

=MAX(1, CEILING.MATH(SUMPRODUCT(B2:B5 / C2:C5)))
=MAX(1; CEILING.MATH(SUMPRODUCT(B2:B5 / C2:C5)))

CEILING.MATH with its default significance of 1 rounds up to a whole number too; on positive numbers it is interchangeable with ROUNDUP(..., 0).

MasterExcel 2021 or later

Aging bands of 30 days, with invoices not yet due

On the receivables aging list at 31 December 2025 each open invoice has a number of days past due. You want the upper limit of its 30-day band: 47 days belongs in the 60 band. An invoice that is not yet due has negative days past due and belongs in the current band (0), not in a band of minus 30.

A negative num_digits does not give multiples of 30, so the formula has to scale by the band size, and the sheet must stay readable for whoever inherits it.

ABCDEFG
1Invoice no.RelationDays past dueBandBand size (days)3030
2FA-25-0891D100147476060=LET( days, C2, step, $F$1, units, days / step, whole, IF(days < 0, ROUNDDOWN(units, 0), ROUNDUP(units, 0)), whole * step )=LET( days; C2; step; $F$1; units; days / step; whole; IF(days < 0; ROUNDDOWN(units; 0); ROUNDUP(units; 0)); whole * step )
3FA-25-0902D10143030
4FA-25-1033D10079191
5FA-25-1120D1022-12-12
6FA-25-0710D1003200200
7
Formula cell (D2)Input

Formula in D2

=LET(
  days, C2,
  step, $F$1,
  units, days / step,
  whole, IF(days < 0, ROUNDDOWN(units, 0), ROUNDUP(units, 0)),
  whole * step
)
=LET(
  days; C2;
  step; $F$1;
  units; days / step;
  whole; IF(days < 0; ROUNDDOWN(units; 0); ROUNDUP(units; 0));
  whole * step
)

Result: 6060

Step by step
  1. LET labels the pieces: days is 47 from C2, step is 30 from $F$1. units divides them: 47 / 30 = 1.5667 bands of 30 days.
  2. whole checks the sign. 47 is not below zero, so ROUNDUP(1.5667, 0) runs: there is a remainder, so 2.
  3. The last line scales back: 2 × 30 = 60. The invoice sits in the 60 band.

  4. Copy down:

    • D3: 30 / 30 = 1 exactly, no remainder, so the band stays 30
    • D4: 91 / 30 = 3.0333, one day over the 90 line, so 4 × 30 = 120
    • D6: 200 / 30 = 6.6667, so 7 × 30 = 210
  5. D5 is the invoice not yet due: -12 / 30 = -0.4. The sign test is TRUE, so ROUNDDOWN(-0.4, 0) runs and moves toward zero: 0. Result 0 × 30 = 0, the current band. ROUNDUP would have given -1 × 30 = -30, a band that does not exist.

  6. Change F1 to 60:

    • D2 stays 60 (47 / 60 = 0.7833 gives 1)
    • D4 becomes 120 (91 / 60 = 1.5167 gives 2)
    • D6 becomes 240 (200 / 60 = 3.3333 gives 4)

Watch out: The mirror-image mistake is swapping the branches: ROUNDUP for negatives and ROUNDDOWN for positives. The sheet then shows 30 for the 47-day invoice (aging understated) and -30 for the one not yet due, and every figure looks tidy.

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

Why it works

num_digits only understands powers of ten, so a band of 30 days is reached by dividing first, rounding to a whole number of bands, and multiplying back; LET makes those three moves readable and evaluates the division once. The sign test exists because ROUNDUP always moves away from zero, which would invent a band of -30 for an invoice that is not yet due; ROUNDDOWN moves toward zero and lands it in 0.

Do not use this construction when the band is a power of ten (ROUNDUP(C2, -1) is enough for positive days) or when you only need the number: CEILING.MATH(C2, $F$1) gives the same band for positive and negative days, because it rounds toward positive infinity, and is the shorter alternative.

Keep the LET version when a reviewer must see the sign rule written out, or when the policy for negatives may differ from the ceiling.

More to watch out for

  • If F1 is empty or 0, units divides by zero and every row shows #DIV/0!. Keep the band size in its own labelled cell, as here, and do not type 30 into the formula.
  • Check the client's own band definition. Here day 30 stays in the 30 band and day 31 moves to 60. If the client's list puts day 31 in the 30 band, the bands do not reconcile and the subledger total per band will differ.

Variations

=CEILING.MATH(C2, $F$1)
=CEILING.MATH(C2; $F$1)

The shorter alternative: 60 for the first invoice and 0 for the one not yet due, because CEILING.MATH rounds toward positive infinity.

=ROUNDUP(C2, -1)
=ROUNDUP(C2; -1)

When the band is 10 days instead of 30, the negative num_digits does it alone for positive days: 47 becomes 50. Do not use it on negative days: -12 becomes -20.

Common mistakes

SymptomCauseFix
-12.3 becomes -13A negative variance of -12.3 rounds to -13 when you expected -12.ROUNDUP moves away from zero in both directions. If you want the mathematically higher value for negatives as well, use CEILING.MATH(number, 1), which does round toward positive infinity.
One box too manyROUNDUP(A2 / 12, 0) returns 8 for 84 units, one box too many, and you cannot see why.A2 probably holds 84.00000001, or a text-to-number conversion left a fraction. Check =A2 - INT(A2), and round the input to a sensible precision before dividing.
Every line a cent higherUsing ROUNDUP for money on an invoice, so every line is a cent higher than the customer calculates.Amounts round to the nearest cent with ROUND. Reserve ROUNDUP for quantities, where a fraction genuinely means one more whole item.