A to Z of Excel Functions: The CEILING Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the CEILING function.

The CEILING function

Have you reached your CEILING? This function returns number rounded up, away from zero, to the nearest multiple of significance:

For example, if you are an American (which, let’s be honest is all Microsoft really cares about) and you want to want to avoid using cents in your prices (that makes no cents) and your product is priced at $4.42, use the formula =CEILING(4.42, 0.05) to round prices up to the nearest nickel.

The CEILING function employs the following syntax to operate:

The CEILING function has the following arguments:

• number: this is required and represents the value you wish to round
• significance: this is also required.  This is the multiple used for rounding.

It should be further noted that:

• if either argument is nonnumeric, CEILING returns the #VALUE! error value
• regardless of the sign of number, a value is rounded up when adjusted away from zero.  If number is an exact multiple of significance, no rounding occurs
• if number is negative, and significance is negative, the value is rounded down, away from zero
• if number is negative, and significance is positive, the value is rounded up towards zero.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.