The CEILING.MATH Function is one of the important Mathematical Functions in Excel. The Function can be used in Excel 2003 and later on Excel Versions. Let’s see how this function works
When to Use CEILING.MATH Function?
The CEILING.MATH Function finds out the upper-value round-off for a number. For Example:-
When we apply CEILING>MATH Function with 23.19 as input parament, it returns 24. 24 is the upper value ( ceiling ) round-off.
The additional functionality is provided by CEILING.MATH Function is that:-
- We can get the round off to the nearest multiple of a number.
- We can decide the direction for rounding up the negative numbers. The rounded-off result can either go away from zero or towards zero.
Syntax and Arguments
The following points will explain to you the input parameters required by the CEILING.MATH formula:-
- number – This is the number that we want to round off.
- [significant] – In this argument, we pass the significant digit multiple. The rounded-off result must be a multiple of this number. This is an optional argument. Its default value is 1.
- [mode] – It has an effect only when we supply a negative number to the function. It can have two values:-
- FALSE – The default value is zero. This means the negative digit will be rounded off towards zero. i.e =CEILING.MATHS(-0.1,1,0) will return 0.
- TRUE – The negative digit will be rounded off away from zero. For example =CEILING.MATHS(-0.1,1,0) will return -1
Examples to learn the CEILING.MATHS formula
In this section of the blog, we would learn to apply CEILING.MATHS Formula in Excel
Example 1 – Simplest Example to Learn CEILING.MATHS formula
In this example, we would learn to round off the positive and negative numbers with the default values of significant and mode argument.
Here we have assigned B1, B2, and cell B3 to contain the number, significant, and mode argument respectively.
We take the value in cell B1 as 65.9 and pass it as the number argument of the function
As a result, the function returns the ceiling round off as 66.
Example 2 – Using CEILING.MATH Function for Negative Numbers
There are two cases defined in the mode argument for the negative numbers. if we specify this mode as 1 (TRUE ) then the direction of round-off is away from zero. Else if the mode argument is FALSE ( 0 is the default value ) then the direction of round off is towards zero.
Also Read: Excel ROUNDDOWN Function – Rounding Down
For example, if we use the following values of the function arguments in the CEILING.MATH formula in cell B6:-
As a result, the function returns 0. If we replace the cell B3 value is TRUE then, the function returns -1.
The different results are due to rounding off directions. This is explained in the figure below.
Example 3 – Rounding off to Multiple of N
We can get the round-off result as a multiple of number N defined in the significant argument. The result will be the multiple of number defined in significant.
The round-off for 4.1 is 5. But if we specify that we want this result to be a multiple of 3, then the function will give different results.
As a result, the function returns the nearest round-off for 4.1 which is also a multiple of 3 as 6.
This brings us to the end of the blog.
Thank you for reading.