CEILING vs ROUNDUP in Excel: Round to a Step or a Digit

The short answer to CEILING vs ROUNDUP: both round a number up, but CEILING rounds up to a step you choose, like 0.25, 5 or a pack of 6, while ROUNDUP rounds up to a number of digits, like 1 decimal place. They give the same result for steps such as 0.1 or 100, and different results for everything else, including negative numbers. This comparison is for anyone pricing, billing time or ordering stock who needs the right "always round up" formula.

CEILING vs ROUNDUP at a glance#

The second argument is the key difference: a step size for CEILING, a digit count for ROUNDUP.

How CEILING and ROUNDUP work#

Both functions take a number and a second argument, but they read that second argument in different ways.

How CEILING works#

CEILING returns the smallest multiple of the step that is greater than or equal to the number.

=CEILING(17.32,0.25)
=CEILING(23,6)
=CEILING(4.12,0.05)

These return 17.5, 24 and 4.15. The step is called the significance. Think of it as the size of a box: CEILING finds how much space you need when you can only use whole boxes. 17.32 hours fits into 70 quarter-hours, which is 17.5 hours.

Older Excel versions required the number and the step to have the same sign. Since Excel 2010, a negative number with a positive step works and rounds toward zero. A positive number with a negative step still returns #NUM!.

CEILING.MATH, from Excel 2013, has a step that defaults to 1 and a third argument, mode, for negative numbers. =CEILING.MATH(-2.3) returns -2, and =CEILING.MATH(-2.3,1,1) returns -3. The guide to CEILING vs FLOOR covers rounding down to a step as well.

How ROUNDUP works#

ROUNDUP rounds a number up, away from zero, to the number of digits you give it.

=ROUNDUP(4.12,1)
=ROUNDUP(3.14159,2)
=ROUNDUP(1234,-2)

These return 4.2, 3.15 and 1300. The digits argument works like ROUND's:

• Positive digits count decimal places: 1 means tenths, 2 means hundredths.
• Zero rounds up to a whole number.
• Negative digits round to the left of the decimal point: -1 means tens, -2 means hundreds.

ROUNDUP belongs to the ROUND family, with ROUND and ROUNDDOWN. The guide to ROUND vs ROUNDUP vs ROUNDDOWN compares all three, and the ROUND function guide covers the shared digits argument.

The same numbers rounded both ways#

Here is how the two functions handle the same values:

Two patterns stand out. When the step is a power of ten, both functions agree. When it isn't, CEILING does it in one step, while ROUNDUP needs a divide-round-multiply workaround. And for the number of packs, ROUNDUP on the division is often the simpler formula.

Negative numbers: where they really differ#

"Up" means something different to each function when the number is negative.

• ROUNDUP rounds away from zero. -2.3 becomes -3, which is a larger amount but a smaller number.
• CEILING with a positive step rounds toward zero, which is up on the number line. -2.3 becomes -2.

Which is right depends on the data. For a refund of -2.30 that should round to a whole -3.00, ROUNDUP gives the larger amount. For a temperature of -2.3 that must be rounded up to the next whole degree, CEILING gives -2. If you need CEILING to round away from zero, use =CEILING.MATH(A2,1,1) or a negative step, =CEILING(-2.3,-1), which returns -3.

INT and TRUNC have a similar split for negatives; see INT vs TRUNC.

When to use CEILING#

Use CEILING when the target is a step that isn't a power of ten:

• Prices that must end in 5 or 9 cents, or round up to the next 0.05.
• Time billing in blocks of 15 minutes, 6 minutes or quarter hours.
• Packs and boxes: order quantities in multiples of the pack size.
• Shipping and capacity: weight bands of 0.5 kg, rooms of 12 people.
• Price endings: to make every price end in .99, round up to the next whole number and take off a cent. With 12.30 in A2, =CEILING(A2,1)-0.01 returns 12.99. Check prices that are already whole numbers, though: 13.00 would become 12.99, which is lower than the original.

If you sometimes need to round down to the same step, FLOOR is CEILING's partner and takes the same arguments: =FLOOR(17.32,0.25) returns 17.25. Keeping CEILING and FLOOR side by side in a sheet makes it obvious which direction each column rounds.

When to use ROUNDUP#

Use ROUNDUP when the target is a number of decimal places or a power of ten:

• Cleaning up decimals: always round tax or fees up to 2 decimal places, =ROUNDUP(A2,2).
• Whole units: people, vehicles or days, =ROUNDUP(A2,0).
• Counts of containers: =ROUNDUP(units/pack_size,0) gives the number of packs directly.
• Shared files: ROUNDUP behaves the same in every version and has no sign rules to remember.

For rounding to the nearest step in either direction, rather than always up, see how to round to the nearest 5, 10 or 0.5.

Common mistakes with CEILING and ROUNDUP#

These slips give wrong or surprising results:

1. Treating the second argument the same. =ROUNDUP(17.32,0.25) doesn't round to quarters: Excel treats 0.25 digits as 0 and returns 18.
2. Using ROUNDUP for steps. =ROUNDUP(4.12,2) returns 4.12, not 4.15. Use CEILING for a 0.05 step.
3. A negative step with a positive number. =CEILING(2.3,-1) returns #NUM!.
4. Forgetting what "up" means for negatives. Check a negative value in your data before you fill the formula down.
5. Formatting instead of rounding. Showing fewer decimals with the Decrease Decimal button changes only the display, not the stored value. Totals can then look wrong by a cent. The guide to Excel rounding functions explains the difference.

Our recommendation#

Choose by the shape of the target. If you can describe it as "a multiple of X", such as 0.25, 5 or a pack of 6, use CEILING. If you can describe it as "N decimal places" or "the next hundred", use ROUNDUP, which reads more clearly to most people and has no sign rules. When negative numbers are possible, test one before you trust either function.

In short: CEILING for steps, ROUNDUP for digits.

Practise it#

Rounding questions are common in Excel tests, and the difference between rounding and formatting trips people up. Practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet: you control decimals with real formulas and the sheet checks each answer instantly. Then try the CEILING exercise on this page to round billed hours up to the next quarter.