ROUNDUP / ROUNDDOWN / FLOOR / CEILING
Four ways to round in a chosen direction, instead of to the nearest value the way plain ROUND does.
ROUND looks at the next digit and decides which way to round; these four never make that decision. ROUNDUP always rounds away from zero and ROUNDDOWN always rounds toward zero, regardless of what the next digit actually is. FLOOR and CEILING work the same directional way but round to a chosen multiple rather than a chosen number of decimal places, which is what makes them the natural fit for "round up to the next full box of 12" or "round down to the nearest 15-minute slot."
The direction matters most exactly when ROUND's "nearest" behavior would give a physically wrong answer: you can't ship 10.4 boxes, and a plain ROUND might round that down to 10 when the real order needs 11.
Syntax
| Argument | Description |
|---|---|
| number | Required for all four. The value to round. |
| num_digits | ROUNDUP/ROUNDDOWN: Required. How many decimal places to keep: 0 rounds to a whole number, a negative number rounds to tens/hundreds. |
| significance | FLOOR/CEILING: Required. The multiple to round down to (FLOOR) or up to (CEILING), e.g. 5 rounds to the nearest 5. |
A real example: always ordering enough full boxes
101 units needed, packed 12 to a box; a fractional box still has to be ordered as a whole box:
101 divided by 12 is 8.42 boxes, and ROUNDUP always rounds away from zero, giving 9 boxes regardless of how small the fractional remainder is; ROUND would have rounded that same 8.42 down to 8, one box short of the actual order.
Worked examples
| A | B | |
|---|---|---|
| 1 | Units needed | Per box |
| 2 | 101 | 12 |
| A | |
|---|---|
| 1 | Total weight |
| 2 | 47.8 |
| A | |
|---|---|
| 1 | Minutes |
| 2 | 142 |
| A | |
|---|---|
| 1 | Attendees |
| 2 | 212 |
Common mistakes
- Using ROUND when the direction actually matters: you can't ship 10.4 boxes, and ROUND(10.4,0) gives 10, not the 11 a real order needs.
- Giving FLOOR/CEILING a significance of 1 by habit when a whole-number round with ROUNDUP/ROUNDDOWN already does the job more simply.
- Mixing up which of FLOOR or CEILING goes with "at least this much" versus "no more than this much"; FLOOR can round a result below a required minimum.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice rounding functions →