Excel CEILING and FLOOR: Round Up or Down to a Multiple
Round a number up or down to the nearest multiple of something you choose.
ROUND rounds to a number of decimal places. CEILING and FLOOR round to a multiple of whatever you name — the next 5, the next 50, the next 0.25 — which is a different question and the one real pricing and logistics keep asking.
CEILING always goes up to the next multiple and FLOOR always goes down to the previous one, regardless of how close the number already is. Boxes that hold twelve items need CEILING, because eleven items still needs a whole box. A discount that must land on a clean five-pound step needs FLOOR, because rounding up would give away money.
MROUND is the third member of the family and goes to the *nearest* multiple in either direction, which is what you want for display rather than for a rule.
Syntax
=CEILING(number, significance) =FLOOR(number, significance)Arguments
numberRequired- The value to round.
significanceRequired- The multiple to round to. 5 rounds to the nearest five; 0.25 rounds to quarters. It must have the same sign as number in the classic versions.
The example data
Headers in row 1, data in A2:D6. C4 is blank and C6 holds text.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sensor | Reading | Calibration | Batch |
| 2 | North inlet | 17 | 3 | 12 |
| 3 | South inlet | -4 | 5 | 7 |
| 4 | Header tank | 63 | 12 | |
| 5 | Overflow | 8 | 2 | 9 |
| 6 | Return line | -21 | n/a | 7 |
Worked examples
=CEILING(B2, 5)Result: 20
17 up to the next multiple of five.
=FLOOR(B2, 5)Result: 15
The same number down to the previous multiple.
=CEILING(B4, 10)Result: 70
63 up to the next ten. Note it goes up even though 63 is closer to 60 — that is the difference from MROUND.
=CEILING(B4/12, 1)Result: 6
The packing calculation: 63 items into boxes of 12 needs six boxes, because five and a quarter boxes is not a thing.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: In the classic CEILING and FLOOR, number and significance have opposite signs.
How to fix it: Use CEILING.MATH and FLOOR.MATH, which handle negatives sensibly and take an optional mode argument.
#DIV/0!
Why it happens: significance is zero.
How to fix it: A multiple of zero has no meaning. Check the cell holding it is not blank.
Rounds the wrong way on negatives
Why it happens: FLOOR on a negative moves away from zero, so FLOOR(-4, 5) is -5. "Down" means down the number line, not toward zero.
How to fix it: Use FLOOR.MATH with its mode argument, or apply the function to ABS and restore the sign.
Tips worth knowing
- MROUND goes to the nearest multiple either way; CEILING and FLOOR always pick a direction.
- CEILING(n, 1) is the same as ROUNDUP(n, 0) and is the standard way to count containers.
- Round time to the nearest quarter hour with =MROUND(A2, "0:15").
- CEILING.MATH and FLOOR.MATH are the modern versions — prefer them for anything that may see negative numbers.
Frequently asked questions
What is the difference between CEILING and ROUNDUP?
ROUNDUP rounds up to a number of decimal places; CEILING rounds up to a multiple. ROUNDUP(17.2, 0) is 18, while CEILING(17.2, 5) is 20. They coincide when the multiple is 1.
How do I round to the nearest 5?
MROUND(A2, 5) goes to the nearest five in either direction. Use CEILING(A2, 5) if it must always go up and FLOOR(A2, 5) if it must always go down — which of the three you want depends on whether you are displaying a number or enforcing a rule.
Why does FLOOR give #NUM! on my data?
The classic FLOOR requires the number and the multiple to share a sign, so a negative value with a positive multiple errors. FLOOR.MATH removes that restriction and is the version to use on any column that can go negative.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.