CEILING function

The CEILING function in Excel is used to round a number up to the nearest specified multiple or significance. It helps ensure that a number is always rounded up to a certain level, which can be particularly useful in financial calculations, pricing, and other scenarios.


CEILING(number, significance)


numberThe number you want to round up to the nearest significance.
significanceThe multiple to which you want to round the number up. This can be a positive or negative number, and it determines the level to which the number is rounded up.

How to use

The CEILING function is straightforward to use. You provide it with a number and a significance, and it rounds the number up to the nearest multiple of the given significance.

Here’s an example:

This formula rounds the number 17 up to the nearest multiple of 5, which is 20.

You can also use negative values for the significance. For instance:

This formula rounds the number 23 up to the nearest multiple of -10, which is -20.

If you need to round to a different level of precision, simply change the significance value. For example:

This formula rounds 42.75 up to the nearest multiple of 0.1, resulting in 42.8.

Remember that if the number is already a multiple of the specified significance, it won’t be rounded up further. The function only rounds up when necessary to reach the nearest multiple.

Additional Information: The CEILING function can be combined with other Excel functions and formulas to perform more complex calculations and adjustments. It is especially useful in financial modeling and pricing calculations.