CEILING
Rounds a number up to the nearest multiple of significance.
CEILING(number, significance)Arguments
numberThe number to roundsignificanceThe multiple to round to
=CEILING(23,5)Rounds up to nearest 5
=CEILING(2.1,1)Rounds up to integer
=CEILING(Price,0.05)Rounds to nearest 5 cents
- •Use CEILING.MATH for more options
- •Useful for pricing calculations
- •Works with time values too
- •Forgetting that CEILING requires a significance argument with the same sign as the number - mismatched signs raise a #NUM! error, the same restriction as FLOOR
- •Assuming CEILING always rounds away from zero - for negative numbers, CEILING actually rounds toward zero (less negative), the opposite of what 'ceiling' might suggest
- •Using the legacy CEILING when CEILING.MATH or CEILING.PRECISE would handle mixed-sign arguments more gracefully, avoiding the stricter sign-matching requirement
FLOORRounds down to the nearest multiple of significance instead of up, the mirror function to CEILING.MROUNDRounds to the nearest multiple rather than always rounding up, a middle ground between FLOOR and CEILING.CEILING.MATHThe modern replacement for CEILING, with more flexible handling of negative numbers and significance values.Why does CEILING return a #NUM! error?
The number and significance arguments likely have mismatched signs - a positive number needs a positive significance, and a negative number needs a negative significance.
Does CEILING always round away from zero?
No - for positive numbers it rounds up away from zero, but for negative numbers it rounds up toward zero (less negative), which can be counterintuitive.
Should I use CEILING or CEILING.MATH?
CEILING.MATH is the modern version and handles negative numbers more flexibly without raising errors - CEILING is kept mainly for compatibility with older files.
Need to translate a formula using CEILING?
Use our translator to convert your complete formula