=ROUNDUP(A2,0) always rounds away from zero, so 2.1 becomes 3. =ROUNDDOWN(A2,0) always rounds toward zero, so 2.9 becomes 2. Both take the same arguments as ROUND: the number, then how many digits to keep.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Value | ROUNDUP | ROUNDDOWN | ROUND |
| 2 | 2.1 | 3 | 2 | 2 |
| 3 | 2.9 | 3 | 2 | 3 |
| 4 | 2.5 | 3 | 2 | 3 |
| 5 | -2.1 | -3 | -2 | -2 |
| 6 | -2.9 | -3 | -2 | -3 |
Look at rows 5 and 6: ROUNDUP turns -2.1 into -3, because "up" means away from zero, not toward the larger number. ROUNDDOWN turns -2.9 into -2. Change A2 to 2.0001 and ROUNDUP still gives 3: any amount past the kept digits counts.
ROUNDUP and ROUNDDOWN syntax
=ROUNDUP(number, num_digits)
=ROUNDDOWN(number, num_digits)
num_digits works exactly as in ROUND: 2 keeps two decimals, 0 gives a whole number, and -1, -2, -3 round to tens, hundreds and thousands.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | num_digits | ROUNDUP | ROUNDDOWN | Number | |
| 2 | 2 | 1234.57 | 1234.56 | 1234.561 | |
| 3 | 1 | 1234.6 | 1234.5 | ||
| 4 | 0 | 1235 | 1234 | ||
| 5 | -2 | 1300 | 1200 |
ROUNDUP with 2 digits turns 1234.561 into 1234.57, and with -2 into 1300. ROUNDDOWN with -2 gives 1200. Change E2 and watch both columns.
Round up a division: how many boxes, buses or pages
The most common reason to round up is a count that cannot be fractional. 250 items in boxes of 24 is 10.4 boxes, which means 11 boxes. =ROUNDUP(B2/C2,0) gives that directly.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Items | Per box | Boxes |
| 2 | A-101 | 250 | 24 | 11 |
| 3 | A-102 | 96 | 24 | 4 |
| 4 | A-103 | 97 | 24 | 5 |
96 items fit exactly in 4 boxes, and one more item needs a fifth box. ROUND would give 4 for 97 items and leave one item without a box.
CEILING and FLOOR: round to a multiple
ROUNDUP and ROUNDDOWN round to powers of ten. To round up or down to a multiple of any number (5, 0.25, 12, half an hour), use CEILING and FLOOR:
=CEILING(A2,5)rounds up to the next multiple of 5.=FLOOR(A2,5)rounds down to the previous multiple of 5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Value | Multiple | CEILING | FLOOR |
| 2 | 11 | 5 | 15 | 10 |
| 3 | 15 | 5 | 15 | 15 |
| 4 | 7.3 | 0.25 | 7.5 | 7.25 |
| 5 | 26 | 12 | 36 | 24 |
A value that is already a multiple stays as it is: 15 with a multiple of 5 gives 15 both ways.
CEILING.MATH and FLOOR.MATH (Excel 2013 and later) do the same with one difference: the multiple is optional and defaults to 1, and for negative numbers a third argument picks the direction. =CEILING.MATH(-7.5) gives -7 (up, toward zero), and =CEILING.MATH(-7.5,1,1) gives -8 (away from zero). Plain CEILING and FLOOR with a negative number and a positive multiple work the same way in current Excel; in Excel 2007 they returned #NUM!.
Round a time up to the next 15 minutes
Times are fractions of a day, so CEILING and FLOOR round them too. Give the multiple as a time: TIME(0,15,0) is 15 minutes.
| A | B | C | |
|---|---|---|---|
| 1 | Logged | Billed (up) | Paid (down) |
| 2 | 1:07 | 1:15 | 1:00 |
| 3 | 0:52 | 1:00 | 0:45 |
| 4 | 2:30 | 2:30 | 2:30 |
| 5 | 0:01 | 0:15 | 0:00 |
1:07 is billed as 1:15, and a single minute is billed as a full quarter hour. 2:30 is already a multiple and stays 2:30. For more on time arithmetic, see time calculations.
INT vs TRUNC with negative numbers
Both drop the decimals of a positive number: =INT(4.7) and =TRUNC(4.7) both give 4. They split on negative numbers. INT rounds down to the next lower whole number, so -4.2 becomes -5. TRUNC cuts the decimals off, so -4.2 becomes -4, the same as ROUNDDOWN.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Value | INT | TRUNC | ROUNDDOWN |
| 2 | 4.7 | 4 | 4 | 4 |
| 3 | 4.2 | 4 | 4 | 4 |
| 4 | -4.2 | -5 | -4 | -4 |
| 5 | -4.7 | -5 | -4 | -4 |
TRUNC also takes a number of digits: =TRUNC(A2,2) keeps two decimals and cuts the rest. Use INT when you mean "the whole number at or below", for example whole days in a date-time value; use TRUNC when you mean "the digits before the decimal point".
Try it: pallets and price steps
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Shipment | Cartons | Per pallet | Pallets |
| 2 | S1 | 130 | 40 |
Your turn: In D2, calculate how many pallets shipment S1 needs: the cartons in B2 divided by the cartons per pallet in C2, rounded up to a whole pallet.
| A | B | C | |
|---|---|---|---|
| 1 | Price | Step | Rounded up |
| 2 | 3.62 | 0.25 |
Your turn: In C2, round the price in A2 up to the next multiple of the step in B2.
Which one to use
| You want | Formula | 7.32 gives | -7.32 gives |
|---|---|---|---|
| Up, 1 decimal | =ROUNDUP(A2,1) | 7.4 | -7.4 |
| Down, 1 decimal | =ROUNDDOWN(A2,1) | 7.3 | -7.3 |
| Up to a multiple of 0.5 | =CEILING.MATH(A2,0.5) | 7.5 | -7 |
| Down to a multiple of 0.5 | =FLOOR.MATH(A2,0.5) | 7 | -7.5 |
| Lower whole number | =INT(A2) | 7 | -8 |
| Drop the decimals | =TRUNC(A2) | 7 | -7 |
The mistake to avoid: using ROUNDUP when you meant "round to the nearest". =ROUNDUP(B2,0) on 3.01 hours bills 4 hours. If the rule is "nearest", use ROUND; if it is "next quarter hour", use CEILING with the step.
Frequently Asked Questions
How do I always round up in Excel?
Use =ROUNDUP(A2,0) for a whole number or =ROUNDUP(A2,2) for two decimals. Any amount past the kept digits rounds up, so 2.01 becomes 3 with 0 digits.
What is the difference between ROUNDUP and CEILING?
ROUNDUP counts digits: =ROUNDUP(A2,-1) goes up to the next 10. CEILING goes up to the next multiple of any number: =CEILING(A2,5) goes up to the next 5 and =CEILING(A2,0.25) to the next quarter.
What is the difference between INT and TRUNC in Excel?
They agree on positive numbers but not on negative ones. =INT(-4.2) rounds down to -5, the next lower whole number, while =TRUNC(-4.2) cuts the decimals off and gives -4.
How do I remove decimals without rounding in Excel?
Use =TRUNC(A2) or =ROUNDDOWN(A2,0): 7.99 becomes 7. To keep two decimals and cut the rest, use =TRUNC(A2,2), so 7.999 becomes 7.99.
How do I round up to the nearest 5 in Excel?
Use =CEILING(A2,5) (or =CEILING.MATH(A2,5)): 11 becomes 15 and 15 stays 15. To round down to a multiple of 5, use =FLOOR(A2,5).
