Excel CEILING.MATH function - Returns a Number Rounded up to the Nearest Level of Significance


The CEILING.MATH function rounds a number up (toward the ceiling) to the nearest integer or to the nearest multiple of significance. 

The Syntax for the CEILING.MATH function is:
    CEILING.MATH(number, [significance], [mode]

Argument Descriptions:
    Number is the value to be rounded up
    Significance is the multiple to which the Number is to be rounded.
    Mode is the number you are dividing by.

In Excel 2010 there was a CEILING function and a CEILING.PRECISE function.  These two functions have been combined into the CEILING.MATH function.  The CEILING function still remains.  The CEILING.PRECISE function also still exists but it has been hidden to discourage its use.

The CEILING.MATH function rounds a number up to the nearest multiple of significance unless the number is negative and the mode argument is used and is a non-zero number.

The significance and mode arguments are optional. If only the number argument is used the CEILING.MATH function will make the number go up (to the ceiling) to the next integer.

Ceiling.Math function - raise to penny

The CEILING.MATH function can be used to ensure that numbers only appear in certain increments. Maybe you only want numbers to appear in increments of 10, such as 10, 20, 30, etc. If you enter the formula =CEILING.MATH(A1,10) in cell B1 then B1 will have a resulting value of 10 if cell A1 contains any positive value that is less than or equal to 10. Cell B1 will have a resulting value of 20, if cell A1 contains any value between 11 and 20 inclusive.

You could use the CEILING.MATH function to round up decimal values to the nearest nickel so that all the prices for your products end with a nickel such as 4.05, 4.10, 4.15, etc.

If you want to round your prices up to the nearest nickel and your product is priced at $2.03, the formula =CEILING.MATH(2.03,.05) will change the price to 2.05.

As you can see the CEILING.MATH function doesn’t really do any rounding it just moves a number up to the next significant number.

CEILING.MATH function Table shows values raised up to nearest penny.

CEILING.MATH function Table shows values raised up to nearest nickel.

CEILING.MATH function Table shows values raised up to nearest quarter.

CEILING.MATH function Table shows values raised up to nearest hundred.

CEILING.MATH function Table shows values raised up to nearest thousand.

The CEILING.MATH function rounds a number even if you enter a negative value for the significance argument.

CEILING.MATH function Table shows values raised up to nearest thousand.

Any nonzero value used for the mode argument will reverse the normal direction for the CEILING.MATH function by making a negative number round down. The mode argument has no effect on positive numbers.

 Ceiling Math function table shows negative mode makes number round down

The CEILING.MATH function also works well for determining how many containers you will need to hold a given number of items. Looking at row 2 in Figure ___ we have 375 items. Only 24 items can fit inside each of our containers. An extra container will be needed to hold any extra fractional part of 24.

 We will first use the CEILING.MATH function to round up the Number of Items to the nearest multiple of 24. The formula =CEILING.MATH(A2,B2) rounds up the value 375 to the nearest multiple of 24 which is 384.

 Now we can take that value and divide it by the number of containers which is 24. This gives us a result of 16 which is the number of containers we will need to hold the 375 items. The formulas entered in column C are displayed in column D.

Ceiling Math function used to compute number of containers needed

Practice for Using Ceiling.Math function to determine the Number of Containers Needed

You can practice using the CEILING.MATH function by working on the interactive worksheet below.