Math · Significance rounding

CEILING vs FLOOR

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.

fx
CEILING(…)  vs  FLOOR(…)
7
measured scenarios
any
significance value
1
HF gap: mode arg
0
external dependencies
▶ Try both live See the feature matrix
🏆Verdict: Opposite directions to a multiple — choose by whether you need the next or previous unit.

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.

CEILING strengths

  • Rounds up to the next multiple of any significance
  • Negative numbers move toward +∞ (-23,5 → -20)
  • Decimal significance works (7.3,0.5 → 7.5)
  • Ideal for pricing up, packaging units, capacity planning

FLOOR strengths

  • Rounds down to the previous multiple of any significance
  • Negative numbers move toward -∞ (-23,5 → -25)
  • Decimal significance works (7.3,0.5 → 7)
  • Ideal for floor pricing, batch limits, conservative allocation

Feature-by-feature matrix

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.

CapabilityCEILINGFLOORWhy it matters
Arguments✓ (number, significance)✓ (number, significance)Identical core signature.
Direction✓ up to next multiple✓ down to previous multiple23,5 → 25 vs 20.
Negative numbers✓ toward +∞✓ toward -∞-23,5 → -20 vs -25.
Decimal significance✓ e.g. 0.5✓ e.g. 0.57.3,0.5 → 7.5 vs 7.
On-multiple value✓ unchanged✓ unchangedCEILING(25,5)=FLOOR(25,5)=25.
Zero significance✗ #DIV/0! in HF✗ #DIV/0! in HFHF returns #DIV/0! at significance=0; real Excel returns 0 (measured gap).
3rd mode argument✗ #N/A in HyperFormula✗ #N/A in HyperFormulaReal 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+.

Syntax, side by side

significance is the multiple you round to — any positive number. The optional mode argument (real Excel only) controls negative-number direction.

CEILING

=CEILING(number, significance, [mode])
numberThe value to round up to a multiple.
significanceThe multiple to round to (e.g. 5, 0.5, 100). Must be positive for predictable results.
modeOptional, real Excel only: controls direction on negatives. NOT supported in HyperFormula (#N/A).
=CEILING(23,5) → 25

FLOOR

=FLOOR(number, significance, [mode])
numberThe value to round down to a multiple.
significanceThe multiple to round to (e.g. 5, 0.5, 100).
modeOptional, real Excel only: controls direction on negatives. NOT supported in HyperFormula (#N/A).
=FLOOR(23,5) → 20

Live head-to-head playground

One editable dataset, two formulas. Change the significance to round to nickels, half-units, hundreds — both sides re-evaluate in the same engine.

A1Sample data — click any cell to edit; both formulas re-evaluate live

CEILING

—

FLOOR

—
HyperFormula 3.5 · live

Every difference, measured

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.

Nearest multiple of 5

Round 23 to a multiple of 5, up vs down.
CEILING
=CEILING(23,5)
25
FLOOR
=FLOOR(23,5)
20
To the next multiple of 5: CEILING(23,5) = 25 (up), FLOOR(23,5) = 20 (down). 23 sits between 20 and 25; each function picks its own end.

Negative number

Round -23 to a multiple of 5.
CEILING
=CEILING(-23,5)
-20
FLOOR
=FLOOR(-23,5)
-25
Mathematical direction on negatives: CEILING(-23,5) = -20 (toward +∞), FLOOR(-23,5) = -25 (toward -∞). Note this differs from ROUNDUP/ROUNDDOWN, which key off zero rather than infinity.

Decimal significance (0.5)

Round 7.3 to a multiple of 0.5.
CEILING
=CEILING(7.3,0.5)
7.5
FLOOR
=FLOOR(7.3,0.5)
7
Decimal significance: CEILING(7.3,0.5) = 7.5, FLOOR(7.3,0.5) = 7. The step is 0.5, so results land on half-units.

Whole-unit significance (1)

Round 7.3 to a multiple of 1.
CEILING
=CEILING(7.3,1)
8
FLOOR
=FLOOR(7.3,1)
7
significance=1 rounds to integers by direction: CEILING(7.3,1) = 8, FLOOR(7.3,1) = 7. Equivalent to ROUNDUP/ROUNDDOWN at num_digits=0 for positives.

Already a multiple

Round 25 to a multiple of 5.
CEILING
=CEILING(25,5)
25
FLOOR
=FLOOR(25,5)
25
Already a multiple: CEILING(25,5) = FLOOR(25,5) = 25. Nothing to round when the value sits on a multiple.

Zero significance

Round 23 with significance 0.
CEILING
=CEILING(23,0)
#DIV/0!
FLOOR
=FLOOR(23,0)
#DIV/0!
Measured engine difference: CEILING(23,0) and FLOOR(23,0) both return #DIV/0! in HyperFormula. Real Excel returns 0 for significance=0. Either way it is a degenerate case — use a positive significance for meaningful rounding.

The mode argument (HF gap)

Round 23 to a multiple of 5 with a 3rd mode argument.
CEILING
=CEILING(23,5,1)
#N/A
FLOOR
=FLOOR(23,5,1)
#N/A
Measured HyperFormula gap: the third mode argument returns #N/A in both CEILING(23,5,1) and FLOOR(23,5,1). Real Excel supports mode to control negative direction; this engine does not. Labeled honestly, not hidden.

Engine benchmark

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 sizeCEILINGFLOOR
1,000 rows × 200 lookups3.74 ms3.12 ms
5,000 rows × 200 lookups3.79 ms3.06 ms
10,000 rows × 200 lookups2.63 ms2.86 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Choose by whether you need the next multiple up or the previous multiple down.

SituationUseWhy
Price up to a nickel / .99 tierCEILINGCEILING pushes to the next pricing unit.
Floor price / conservative quoteFLOORFLOOR drops to the previous unit, never over.
Packaging / case quantitiesCEILINGRound up to full cases so you never short-ship.
Batch limits / max allocationFLOORRound down to stay within a hard cap.
Round to half-units (0.5)EitherBoth accept decimal significance; pick the direction you need.
Negative values, direction mattersEitherCEILING → +∞, FLOOR → -∞; verify the sign behavior you want.
You need the mode argumentEitherNeither in HyperFormula — use real Excel or CEILING.MATH/FLOOR.MATH there.
Value already a multipleEitherBoth return it unchanged.

CEILING ↔ FLOOR migration

Swapping direction is a one-word edit; significance stays. The mode argument is real-Excel-only and unsupported here.

TaskCEILING / old wayFLOORWhat 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).

Frequently asked questions

What is the difference between CEILING and FLOOR?
Direction to a multiple. CEILING rounds up to the next multiple of significance; FLOOR rounds down to the previous one. CEILING(23,5) = 25 and FLOOR(23,5) = 20. The significance is the unit you round to — 5, 0.5, 100, anything positive.
How is this different from ROUNDUP/ROUNDDOWN?
ROUNDUP/ROUNDDOWN round to a decimal place (num_digits); CEILING/FLOOR round to a multiple (significance). ROUNDUP(23,0) = 23 (already integer), but CEILING(23,5) = 25 because it targets multiples of 5, not decimal places. Use significance when your unit is 5, 0.5, 100, etc.
What happens with negative numbers?
CEILING moves toward positive infinity: CEILING(-23,5) = -20. FLOOR moves toward negative infinity: FLOOR(-23,5) = -25. This is the mathematical ceiling/floor definition. Real Excel's mode argument can change this, but HyperFormula does not support mode.
Does HyperFormula support the mode argument?
No — measured. CEILING(23,5,1) and FLOOR(23,5,1) both return #N/A in the HyperFormula engine on this page. Real Excel uses the third mode argument to control negative-number direction. If you need mode, run it in Excel or use CEILING.MATH/FLOOR.MATH (Excel 2013+).
What if significance is 0?
It depends on the engine. In real Excel, CEILING(23,0) and FLOOR(23,0) return 0. In the HyperFormula engine on this page, both return #DIV/0! (measured). Either way a zero significance is degenerate — use a positive significance for meaningful rounding.
Can significance be a decimal like 0.5?
Yes. CEILING(7.3,0.5) = 7.5 and FLOOR(7.3,0.5) = 7. Decimal significance lets you round to half-units, quarter-units, or any fractional step your problem requires.