CEILING.MATH Function in Excel – Usage with Examples

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:-

=CELING.MATH(23.19) 

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

=CEILING.MATHS(number,[significance],[mode])

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
infographics CEILING.MATH function in excel

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.

simplest example to learn CEILING.MATH function

We take the value in cell B1 as 65.9 and pass it as the number argument of the function

=CEILING.MATH(B1)

As a result, the function returns the ceiling round off as 66.

simplest example to learn CEILING.MATH function explanation

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.

For example, if we use the following values of the function arguments in the CEILING.MATH formula in cell B6:-

=CEILING.MATH(B1,B2,B3)
simplest example 2 to learn CEILING.MATH function

As a result, the function returns 0. If we replace the cell B3 value is TRUE then, the function returns -1.

simplest example 2 to learn CEILING.MATH function result

The different results are due to rounding off directions. This is explained in the figure below.

arguments ceiling.math function

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.

For Example:-

simplest example 3 to learn CEILING.MATH function

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.

simplest example 3 to learn CEILING.MATH function explanation

As a result, the function returns the nearest round-off for 4.1 which is also a multiple of 3 as 6.

arguments ceiling.math function part 2

This brings us to the end of the blog.

Thank you for reading.

Leave a Comment