CEILING Function in Excel
CEILING Function is very similar to the floor function in excel but the result is just opposite of the floor function, where floor function gave us the result to the less significance ceiling formula gives us the result to the higher significance so for example if we have number as 10 and significance as 3 then 12 will be the result.
- number: It is the value that you want to round off.
- significance: It is the multiple that we want to round up.
How to Use the CEILING Function in Excel?
Suppose take a set of positive integers as number arguments, and positive numbers as significance then apply CEILING Excel function on it as shown in the below table and o/p will be shown in the result as follows.
In this example, we take a set of negative integers as number arguments and positive numbers as significance then apply CEILING excel formula on it as shown in the below table, and o/p will be shown in the result as follows.
In this example, we take a set of negative integers as number arguments, and negative numbers as significance then apply the CEILING excel formula on it.
We can use the excel CEILING formula to highlight the rows in the group of given data, as shown in the below table. Here we will use the CEILING in Excel with ROW and IS SEVEN functions.
Suppose we are given the data below:
And, we wish to highlight the rows in the given groups of 3, we can use the excel formula =ISEVEN(CEILING(ROW()-19,3)/3) in conditional formatting and output will be as follows:
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Things to Remember
- #NUM! error:
- Occurs in MS Excel 2007 or earlier versions and provided the significance argument with a different arithmetic sign from the given number argument, then get #NUM! Error.
- Occurs only if you are using MS Excel 2010/2013, then CEILING functions through #NUM! Error if the given number is positive and the supplied significance is negative.
- #DIV/0! Error occurs when the significance parameter is zero.
- #VALUE! Error occurs when any of the parameters is non-numeric.
- CEILING Function is the same as MROUND, but it always rounds up away from zero.
This has been a guide to the CEILING Function in Excel. Here we discuss the CEILING Formula in excel and how to use the CEILING function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –