Microsoft Excel – CEILING and FLOOR functions

As part of Excel functions discussions, we are going to discuss two functions through this Article; which are CEILING and FLOOR functions.

The CEILING function

This function returns the rounded-up number which is nearest to the specified multiple of significance. It is important to understand that, the return number is based on the specified significance; that means, the return number is always nearest to the multiple of significance; it is NOT nearest to the given significance.

For example, if the given number is 12.32 and the significance value is 2; the next available HIGHER value (round up) which is a multiple of the significance 2 is 14; then the return value would be 14.

The Syntax of the function is like below:

=CEILING(number, significance)

CEILING function takes 2 arguments and both are mandatory. number is the number to which you want to apply the CEILING; and the significance is the multiple to which you want to round.

The FLOOR function

Unlike CEILING function, this function returns the rounded-down number which is nearest to the multiple of the significance. For example, take some value mentioned in the above example; 12.32 and the significance value is 2, and this function returns the round down value which is 12.

=FLOOR(number, significance)

Like CEILING function, it also takes 2 mandatory arguments and returns the round-down number which is the multiple of the given significance.

Commonalities in both these functions

Both these functions can take negative and positive numbers.
These functions return the #VALUE! error, if you pass the non-numeric value (for example, text value) through these arguments.
These functions return the #NUM! error, if the significance value is a negative value and the number value is a positive value.

🙂 Sahida