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 function 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.
CEILING Function (Table of Contents)
CEILING Function in Excel
CEILING function is categories as Math or Trig function in excel returns a number rounded up based on a multiple of significance.
The CEILING function in Excel is very easy and helpful. CEILING Formula in Excel helps in setting the price in currency conversion and also helps us round up the numbers as per the requirement.
The CEILING Formula in Excel was first introduced in Excel version 2000.
CEILING Formula in Excel
CEILING Formula in Excel has two compulsory parameters i.e. number and significance.
- 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?
CEILING in Excel is very simple and easy to use. Let understand the working of CEILING function in Excel by some examples. CEILING Formula in Excel can be used as worksheet function and as VBA function.
CEILING function as worksheet function.
CEILING in Excel Example #1
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.
CEILING in Excel Example #2
In this example, we take a set of negative integers as number arguments and positive numbers as significance then apply CEILING function on it as shown in the below table and o/p will be shown in the result as follows.
CEILING in Excel Example #3
In this example, we take a set of negative integers as number arguments and negative numbers as significance then apply CEILING function on it.
CEILING in Excel Example #4
We can use the CEILING function in Excel to highlight the rows in the group of given data as shown in the below table. Here we will use CEILING in Excel with ROW and ISEVEN 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 formula =ISEVEN(CEILING(ROW()-19,3)/3) in conditional formatting and output will be as follows:
CEILING in Excel can be used as a VBA function.
Dim dblCEILING As Double // declare a variable as double
dblCEILING =application.worksheetfunction.ceiling(10,5) //save the CEILING function o/p in dblCEILING variable
Msgbox(dblCEILING) //Print the output CEILING in Message box.
Output “5” will be printed in the message box.
Things to Remember About the CEILING Function in Excel
- #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 in Excel is same as MROUND but it always rounds up away from zero.
This has been a guide to CEILING Function in Excel. Here we discuss the CEILING Formula in excel and how to use CEILING function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –