How to use the Excel CEILING function
In this guide, we are going to talk about the Excel CEILING function and some examples of how to use it.
We will cover:
- What is the CEILING function in Excel?
- The formula that is used
- Examples of the CEILING function in action
- Common errors and how to resolve them
What is the CEILING function in Excel?
The Excel CEILING function is a formula within Excel that sits under the Math and Trigonometry category.
This formula takes a number, and rounds it up to the next possible number based on the nearest multiple of significance.
What this means, is that if the existing number we are working with cannot be divided by the number we specify in a way that leaves us with zero, it will count up until it reaches a number that does – whether that be a whole number or one with decimals.
This formula can also come in handy when working with bigger datasets where we need to ensure that the numbers we are working with are rounded to the nearest multiple of significance and want to perform a quick spot check. If a number needs to be divisible by 3 then any numbers that are not will be rounded up, but if the number already is (for example 9) then the resulting output of the formula will simply be that same number.
The CEILING formula
The formula in Excel to run the CEILING function is quite a simple one. It is as follows:
=CEILING(number, significance)
Lets break this down.
Number: The number portion of the formula is simply the number you are looking to round up (or as we discussed – confirm that it is indeed divisible by the next part of the formula)
Significance: This is the number we enter that we want to set as the multiple to use when rounding up.
There are a few ways we could use this formula, depending on if we are going to use numbers directly in the formula itself, or if we want to work with values that are already sitting in our spreadsheet. See below examples.
=CEILING(10,3) – takes the number 10, and rounds it up to the nearest number divisible by 3
=CEILING(B5,5) – takes the number that is sitting within cell B5, and rounds it up (if required) to the nearest number divisible by 5
=CEILING(25,A5) – takes the number 25, and rounds it up to the nearest number divisible by the value in cell A5
=CEILING(C1,C2) – takes the value that is in cell C1, and rounds it up to the nearest number divisible by the value in cell C2
Examples of the CEILING function in action
Lets look at some examples of the Excel CEILING function in action, using a range of values.
Rounding Whole Numbers
The most common use of this formula is to take a whole number, and round it up. Below are five examples of this against a range of values that we have listed out in column B of this file. For this example we have listed a variety of values for the significance portion of this calculation directly in the formula:
Rounding Negative Numbers
The CEILING function in Excel also works with negative numbers. It won’t round down further into the negatives as it it a function purely based on rounding up, so works in a very similar way to the above whole number examples. Lets take a look at some in action:
CEILING isn’t just for whole numbers
The Excel CEILING formula doesn’t just work with whole numbers. There are a range of ways we can work with decimals. For example maybe we are working with calculating a 5 star rating based on a series of averages, and only want to work with whole and half numbers between 0 and 5. In this case simply adding in 0.5 into the significance portion of the formula will work nicely.
Below we have some examples. The top 3 are using our 5 star rating example, with a couple of additional examples using more granular figures of being divisible by 0.1 and 0.4 to help showcase how specific we can really go with this formula:
Working with time values
We can also very easily round up a time based on a multiple. This lets us take specific times and round to the next quarter of an hour, or minute for example. We can even round to multiple of seconds so if we had a time of 16 hours, 37 minutes and 14 seconds, we can round to 16:37:30 if we simply enter 30 seconds as the multiple and get the next closest half minute.
The syntax for this formula is quite simple – we just need to wrap the time in double quotes. See below examples:
Common limitations and errors when working with CEILING in Excel
Now that we have seen some examples of the Excel CEILING formulas and expected outputs, lets have a quick look at some of the more common errors that we can come across with this formula.
The significance field in this formula can only accept positive numbers. If you were to enter -5 for example this would result in a #NUM! error.
The CEILING formula can only round up. If you wanted to round down to the nearest multiple of significance instead then the Excel FLOOR function will do the job here! This formula works in almost exactly the same way as CEILING, with a number and significance.
Using the first example in our series of pictures above, if we ran with FLOOR instead, the output would be 6 rather than 9.
There is also the CEILING.MATH formula which is a bit different and can provide control over rounding up or down.
Summary
This sums up our guide on the Excel CEILING function within Excel. As we have shown, it is a simple but handy and powerful formula to easily provide us with numbers that work with our desired multiples.
This works with whole numbers, decimal numbers, and even has the capability to work with values such as time which provides a lot of real world use cases when working with data.
For more handy Excel tips be sure to check out the following page:
