Excel CEILING Function
The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function, but CEILING always rounds up.
- number - The number that should be rounded.
- multiple - The multiple to use when rounding.
The Excel CEILING function rounds a number up to a given multiple.
Example #1 - round up to nearest 5
To round a number in A1 up to the nearest multiple of 5, you can use a formula like this:
Example #2 - round pricing up to end with .99
CEILING can be a can be useful to set pricing after currency conversion or discounts are applied. For example, the formula below will round a value in A1 to the nearest 1 dollar, then subtract 1 cent, and return a price like $2.99, $5.99, $49.99, etc.
=CEILING(A1,1) - 0.01
Example #3 - round time up to nearest 15 minutes
CEILING can be used to round time up to a given multiple. For example, to round a time in A1 down to the nearest 15 minutes, you can use CEILING like this:
=CEILING(A1,"0:15") // round up to nearest 15 min
Rounding functions in Excel
Excel provides a number of functions for rounding:
- To round normally, use the ROUND function.
- To round to the nearest multiple, use the MROUND function.
- To round down to the nearest specified place, use the ROUNDDOWN function.
- To round down to the nearest specified multiple, use the FLOOR function.
- To round up to the nearest specified place, use the ROUNDUP function.
- To round up to the nearest specified multiple, use the CEILING function.
- To round down and return an integer only, use the INT function.
- To truncate decimal places, use the TRUNC function.
- CEILING works like the MROUND function, but CEILING always rounds up.
- If number is an exact multiple of significance, no rounding occurs.
- If number and significance are both negative, CEILING rounds down, away from zero.
- If number is negative, and significance is positive, CEILING rounds up, towards zero.
- For more control over how CEILING rounds negative numbers, see the CEILING.MATH function.