A to Z of Excel Functions: CEILING

The CEILING function is one of a handful of functions you can use in Excel to round numbers up or down.

CEILING is used to round a number up either to its nearest integer (whole number) or to the nearest multiple of significance.  Not following yet?  You will.  CEILING looks like this:-

=CEILING(number to round, significance to round to)

Lets start with a really simple way of showing how CEILING works by rounding 5.5 up:-

=CEILING(5.5,1)
=6

The function has returned 6 because we have asked the function to return a value to the nearest multiple of significance, which is in this case 1.  It will round the number to the nearest multiple of 1, which is 6.  We can try this with another to show it working this way again:-

=CEILING(4.45,1)
=5

Again, the function has round the figure up to the nearest multiple of 1, in this case 5.

What would happen if we changed the 1 to 2 in the last example?  Any guesses?:-

=CEILING(4.45,2)
=6

Huh?  How come?  It’s because we’ve asked the function to return to the nearest multiple of 2, which is obviously 6 in this case.  Let’s try it with something a little more complicated:-

=CEILING(45,10.5)
=52.5

Getting it yet?  This time the function has taken the number 45 and looked for the next highest multiple of 10.5, which is in this case 52.5.  Pretty handy, isn’t it?  It also works with negative numbers in exactly the same way, but you have to ensure that both criteria are signed (+ or -) in the same way or you’ll get an error.

So, that’s the CEILING function covered – it’s a really useful one, especially when dealing with money and you want to round to, lets say, the nearest 5 pence (or [enter your currency type here]).  Have a try yourself to get it round your head.

Leave a Reply

Your email address will not be published. Required fields are marked *