Where ROUNDUP/ROUNDDOWN round to a place, CEILING and FLOOR round to a multiple — the significance. CEILING rounds up to the next multiple of significance; FLOOR rounds down to the previous one. Round to 5, to 0.5, to 100 — whatever unit your problem demands. Every result below is measured in the HyperFormula engine, including the one place the engine diverges from real Excel.
CEILING(number, significance) rounds up to the next multiple of significance; FLOOR(number, significance) rounds down to the previous one. CEILING(23,5) = 25, FLOOR(23,5) = 20. The significance can be any positive number — 5 for nickels, 0.5 for half-units, 100 for hundreds. On negatives, CEILING moves toward positive infinity (-23 → -20) and FLOOR toward negative infinity (-23 → -25). With significance 0, HyperFormula returns #DIV/0! for both (real Excel returns 0 — a measured engine difference). One further measured gap: HyperFormula does not support the third mode argument that real Excel uses to control negative-number direction — it returns #N/A here, labeled honestly below.
Eight dimensions, measured. They tie on signature and boundary behavior, diverge on direction, and share one honest engine limitation: the mode argument is unsupported in HyperFormula.
| Capability | CEILING | FLOOR | Why it matters |
|---|---|---|---|
| Arguments | ✓ (number, significance) | ✓ (number, significance) | Identical core signature. |
| Direction | ✓ up to next multiple | ✓ down to previous multiple | 23,5 → 25 vs 20. |
| Negative numbers | ✓ toward +∞ | ✓ toward -∞ | -23,5 → -20 vs -25. |
| Decimal significance | ✓ e.g. 0.5 | ✓ e.g. 0.5 | 7.3,0.5 → 7.5 vs 7. |
| On-multiple value | ✓ unchanged | ✓ unchanged | CEILING(25,5)=FLOOR(25,5)=25. |
| Zero significance | ✗ #DIV/0! in HF | ✗ #DIV/0! in HF | HF returns #DIV/0! at significance=0; real Excel returns 0 (measured gap). |
| 3rd mode argument | ✗ #N/A in HyperFormula | ✗ #N/A in HyperFormula | Real Excel supports mode for negative direction; HF does not (measured). |
| Excel version | ✓ 2007+ (all) | ✓ 2007+ (all) | Legacy functions; CEILING.MATH/FLOOR.MATH add mode in 2013+. |
significance is the multiple you round to — any positive number. The optional mode argument (real Excel only) controls negative-number direction.
One editable dataset, two formulas. Change the significance to round to nickels, half-units, hundreds — both sides re-evaluate in the same engine.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=CEILING(23,5)=FLOOR(23,5)=CEILING(-23,5)=FLOOR(-23,5)=CEILING(7.3,0.5)=FLOOR(7.3,0.5)=CEILING(7.3,1)=FLOOR(7.3,1)=CEILING(25,5)=FLOOR(25,5)=CEILING(23,0)=FLOOR(23,0)=CEILING(23,5,1)=FLOOR(23,5,1)Relative engine cost of rounding the same stream to a multiple of 5, up vs down. Identical work; treat timing as noise, not a decision factor.
| Table size | CEILING | FLOOR |
|---|---|---|
| 1,000 rows × 200 lookups | 3.74 ms | 3.12 ms |
| 5,000 rows × 200 lookups | 3.79 ms | 3.06 ms |
| 10,000 rows × 200 lookups | 2.63 ms | 2.86 ms |
Choose by whether you need the next multiple up or the previous multiple down.
| Situation | Use | Why |
|---|---|---|
| Price up to a nickel / .99 tier | CEILING | CEILING pushes to the next pricing unit. |
| Floor price / conservative quote | FLOOR | FLOOR drops to the previous unit, never over. |
| Packaging / case quantities | CEILING | Round up to full cases so you never short-ship. |
| Batch limits / max allocation | FLOOR | Round down to stay within a hard cap. |
| Round to half-units (0.5) | Either | Both accept decimal significance; pick the direction you need. |
| Negative values, direction matters | Either | CEILING → +∞, FLOOR → -∞; verify the sign behavior you want. |
| You need the mode argument | Either | Neither in HyperFormula — use real Excel or CEILING.MATH/FLOOR.MATH there. |
| Value already a multiple | Either | Both return it unchanged. |
Swapping direction is a one-word edit; significance stays. The mode argument is real-Excel-only and unsupported here.
| Task | CEILING / old way | FLOOR | What changed |
|---|---|---|---|
| Round to multiple of 5 | =CEILING(A2,5) | =FLOOR(A2,5) | Same significance; direction flips. |
| Half-unit rounding | =CEILING(A2,0.5) | =FLOOR(A2,0.5) | Decimal significance works in both. |
| Whole-unit ceiling/floor | =CEILING(A2,1) | =FLOOR(A2,1) | significance=1 rounds to integers by direction. |
| Round to hundreds | =CEILING(A2,100) | =FLOOR(A2,100) | Large significance rounds to big units. |
| Negative-direction control | =CEILING(A2,5,1) | =FLOOR(A2,5,1) | mode arg: real Excel only; HyperFormula returns #N/A (measured). |