ISO.CEILING

What it does

ISO.CEILING(number, [significance])

ISO.CEILING(number, [significance]) rounds number upward — toward positive infinity — to the nearest multiple of significance.

ISO.CEILING(x, s) = ⌈ x / |s| ⌉ · |s|

with s defaulting to 1, in which case the function is the plain mathematical ceiling ⌈x⌉.

The defining property, and the reason the function exists, is stated on Microsoft's page:

"The absolute value of the multiple is used, so that the ISO.CEILING function returns the mathematical ceiling irrespective of the signs of number and significance."

So the direction of rounding is always toward +∞, for every combination of signs. The documented examples make the shape explicit: 4.3 rises to 5, −4.3 rises to −4, and 4.3 with a significance of either 2 or −2 gives 6, while −4.3 with either sign of 2 gives −4. Negating the significance changes nothing.

Ceiling is the reflection of floor: ⌈x⌉ = −⌊−x⌋, so everything true of INT has a mirror image here. Domain: all reals for number; any nonzero real for significance. Range: the multiples of |s|. The function is non-decreasing in number and discontinuous at every multiple of |s|, with a jump of exactly |s| — this time from the right, since ceiling is right-continuous where floor is left-continuous.

The name records its origin: this is the rounding rule of ISO/IEC 29500 (the OOXML spreadsheet formula specification), added so that a spreadsheet could express the mathematical ceiling without inheriting the sign-dependent behaviour of the older CEILING.

Arguments

The declared arity is one to two, which agrees with the documented optional second argument.

Both slots are numeric and subject to ordinary to-number coercion (coercion and lifting).

Try it

Quirks

Result and edge cases

Returns Number.

• Zero returns zero, and −1 returns −1: an argument already on a multiple is left alone. Ceiling is idempotent, ⌈⌈x⌉⌉ = ⌈x⌉, and that is the visible consequence.
• A logical argument and numeric text both convert and round. Microsoft's page addresses neither.
• The smallest positive subnormal rounds up to 1 — the smallest strictly positive value becomes the smallest positive integer. This is correct and is the cleanest demonstration that the function rounds up rather than to-nearest.
• The largest finite double is returned unchanged, because every double at or above 2^52 is already an integer and therefore already a multiple of 1.
• An inline array produces an array of the same shape. Note that the module implementing this family carries an open upstream defect stream concerning math scalar/array lifting (BUG-FUNC-017), so array-shaped arguments in this family are an area with recorded historical trouble rather than a settled one.
• An empty range produces #VALUE!.

What is not documented, and is not invented here: what happens when significance is zero. Every multiple of zero is zero, so "the least multiple of 0 that is at least x" exists only for x ≤ 0. Microsoft's page does not state a rule; the neighbouring CEILING.MATH documentation states that a zero significance yields zero, but that is a different function's page and this page will not transfer it. It is the first probe below.

Errors

Microsoft's ISO.CEILING page documents no error return. Errors reachable here come from the shared call model:

How to implement

Numerical notes

Like floor, ceiling introduces no rounding error of its own and inherits every hazard from the discontinuity. The two additions specific to the significance form are worth stating.

The division is where the error enters. ⌈x/|s|⌉·|s| performs a division, a ceiling and a multiplication. Neither the division nor the multiplication is exact in general, so:

1. x/|s| may round to the wrong side of an integer. If x is an exact multiple of s but the quotient rounds to just above the integer, the ceiling adds a whole step and the answer is one multiple too high. This is not hypothetical: it is the standard failure mode of every naive round-to-multiple implementation, and it is why ISO.CEILING(4.2, 0.1) and its relatives are the classic test cases. Decimal significances like 0.1, 0.05 and 0.01 are not representable in binary64, so every such call is asking for the ceiling of a quotient that was already inexact.
2. The final multiplication may not land back on a representable multiple. n·|s| rounds, so the returned value can fail to be exactly divisible by s even when n is correct.

The standard remedies are the ones used throughout careful decimal-rounding work: compute the quotient with a fused multiply-add or a double-double residual to determine the correct side of the integer boundary before rounding; or scale by a power of ten and work in exact integers when the significance is a decimal fraction; or apply a deliberate, documented tolerance so that a quotient within a few ULP of an integer is treated as on it. Each of these is a decision, and what matters for a compatibility implementation is which decision the target made — a question this page cannot answer for Excel.

For the default significance of 1 none of this arises: the operation is IEEE 754's roundToIntegralTowardPositive (C99 ceil, Rust f64::ceil), exact for every finite double, correctly rounded by the standard, and with no intermediate at all. The gap in difficulty between ISO.CEILING(x) and ISO.CEILING(x, 0.1) is the whole story of this function.

Rust code

5 iso_ceiling "ISO.CEILING" (1, 2) [V "number", V "significance"] |s, a, sv| {
    two(s, a, cf::iso_ceiling_kernel)
}

pub fn iso_ceiling_kernel(number: f64, significance: f64) -> Result<f64, WorksheetErrorCode> {
    ceiling_math_kernel(number, significance, 0.0)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

This is the most crowded neighbourhood in the math category, and the differences are entirely about what happens to negatives.

• CEILING.PRECISE — documented with the same rule: absolute value of significance, mathematical ceiling regardless of signs. On the documented behaviour the two are the same function under two names, one carrying the ISO lineage and one the Excel-native naming scheme. This page does not assert that they share a code path or that they agree in every bit; it asserts only that the two published rules coincide.
• CEILING — the legacy function ISO.CEILING was introduced to correct. Its documented rule is sign-dependent, and it is the one that surprises people.
• CEILING.MATH — the modern general form, with an explicit third argument choosing the direction for negative numbers. ISO.CEILING is the fixed-mode case.
• FLOOR.PRECISE, FLOOR.MATH, FLOOR — the downward mirrors, one for one.
• INT — the plain floor. ISO.CEILING(x) and -INT(-x) are the same number.
• MROUND — rounds to the nearest multiple rather than up.
• All seven of the ceiling/floor surfaces are implemented in one shared module in the reference engine, so they are a family by construction there as well as by documentation.

Named variants

No named variants are recorded yet.