CEILING
Round a number up to the nearest multiple of a given significance — the next 0.05, the next 5, the next full crate.
Syntax
CEILING(number, [significance])| Argument | Required | Description |
|---|---|---|
number | Required | The number to round. |
significance | Optional | The multiple to round up to. Defaults to 1 here; Excel requires it. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=CEILING(A2,0.05) | 3.45 | Up to the next 5 cents. |
=CEILING(12,5) | 15 | |
=CEILING(4.2,0.5) | 4.50 | |
=CEILING(-4.2,1) | -4 | A negative number with a positive significance rounds towards zero. |
=CEILING(-4.2,-1) | -5 | With both negative, it rounds away from zero. |
=CEILING(4.2,-1) | #NUM! | A positive number with a negative significance is an error, as in Excel. |
Prices, packs and billing increments
CEILING rounds up to a multiple rather than to a number of decimal places.
That covers the cases ROUNDUP cannot express:
=CEILING(3.42,0.05) → 3.45
=CEILING(12,5) → 15— prices to the next nickel, quantities to the next case of 5, call time to the
next 15-minute block with a significance of 15.
Negative numbers
The sign rules follow Excel. A negative number with a positive significance
rounds up, towards zero; with a negative significance it rounds away from zero.
A positive number with a negative significance has no sensible answer and
returns #NUM!. A significance of 0 returns 0.
Excel compatibility
Matches Excel's CEILING, including its sign rules. The significance is optional here (defaulting to 1); Excel requires it, so write it out in a sheet that will be opened there.