How to Use CEILING.MATH and FLOOR.MATH Functions in Excel

The CEILING.MATH function is a new function that we can see in Microsoft Excel 2013 and later versions. The CEILING.MATH function rounds a specified number up to the nearest integer or nearest specified multiple of significance. The FLOOR.MATH Function is also a new function that we can also see in Excel 2013 and later versions. The FLOOR.MATH function rounds a specified number down to the nearest integer or nearest specified multiple of significance. Both functions provide additional options for rounding negative numbers. In this article, I will show you how to use CEILING.MATH and FLOOR.MATH functions in Excel.

Excel CEILING.MATH Function: Syntax and Arguments

Summary:

• The CEILING.MATH function rounds a specified number up to the nearest integer or nearest specified multiple of significance.
• This function rounds all positive numbers with decimal places up to the nearest integer as its default feature.
• This function rounds all negative numbers with decimal places up towards zero to the nearest integer as its default feature.
• Available from Excel 2013.

Syntax:

The syntax or formula for the CEILING.MATH function of Excel is

Arguments:

Return:

This function will return the nearest rounded integer of a specific number.

Excel FLOOR.MATH Function: Syntax and Arguments

Summary:

• The FLOOR.MATH function also rounds a specified number down to the nearest integer or nearest specified multiple of significance.
• The function also provides additional options for rounding negative numbers.
• The FLOOR.MATH function  allows one to reverse the direction of the rounding for negative numbers by specifying the mode.
• Available from Excel 2013.

Syntax:

The syntax or formula for the FLOOR.MATH function of Excel is

Arguments:

Return:

This function will return the nearest rounded integer of a specific number.

How to Use CEILING.MATH and FLOOR.MATH Functions in Excel

From Excel 2013 and later versions, we can see The CEILING.MATH and FLOOR.MATH functions. We can use these functions in order to allow specific control over the way to round the negative numbers. By specifying the mode, one can control if negative numbers will round towards 0 or away from 0. This depends on what one’s rounding needs are.

In this section, I will show you how to use CEILING.MATH and FLOOR.MATH functions in Excel. For that, I will use the following data set.

1. Using CEILING.MATH Function

Here, we have a range of numbers, and we are going to use the CEILING.MATH function to round up using first a significance of 1 and a mode of 1. Then, a significance of 1 and a mode of -1, and a significance of 1 and a mode of 0. We are then going to use the CEILING.MATH function in conjunction with a significance of 2 and a mode of 1. Then, a significance of 2 and a mode of -1, and a significance of 2 and a mode of 0. The detailed steps are as follows.

Step 1:

• At first, we want to see the effect of a significance of 1 and a mode of 1. So in cell C6, enter the following formula:

Step 2:

• Secondly, upon pressing Ctrl + Enter, the formula will deliver a value of -1, which rounds the negative number -0.331 away from 0.
• Then, using AutoFill, dragging the formula down, since we used relative references, delivers the following results.

Step 3:

• Consequently, since we specified the significance of 1, which is also the default significance, the formula will round the positive numbers towards the nearest integer. That, in the case of 355 is 1, 1.5 is 2, 7.22 is 8, and 10.01 is 11.
• Also, since we specified a mode of 1, the formula will round the negative numbers away from 0. So the formula will round away -0.331  from 0 to -1, -1.5 to -2, and -7.22 to -8. You can see it in the following image.

Step 4:

• Fourthly, in cell D6, we want to see what will happen if we specify a significance of 1, and a mode of -1, so in cell D6, we enter the following formula:

Step 5:

• Afterwards, pressing Ctrl + Enter, the formula will deliver a value of -1 again, which rounds the negative number -0.331 away from 0.
• Then, we will drag the formula down, since we used relative references, and deliver the following results.
• So, we can thus see that specifying either a positive number or a negative number for the mode results in negative numbers will round the number away from 0.

Step 6:

• Afterward, in cell E6, we want to see what will happen if we specify a significance of 1, and a mode of 0. So, in cell E6, we enter the following formula:

Step 7:

• Consequently, upon pressing Ctrl + Enter, a value of 0, which rounds the negative number toward 0.
• Then, drag the Fill Handle to see the results for the lower cells of the column.

We can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers towards 0. So, –0.331 is rounded towards 0, and thus 0 is delivered, -1.5 is rounded towards 0, and thus -1 is delivered. Also, -7.22 is also rounded towards 0, and thus -7 is delivered when using 0 as the mode.

Step 8:

• Furthermore, we now want to see what will happen if we use a significance of 2 and a mode of 1.
• So, in cell C16, we enter the following formula:

Step 9:

• Afterward, upon pressing Ctrl + Enter, the formula will deliver a value of -2, which rounds the negative number away from 0. Since the mode specified was 1, and the formula delivers -2 since this is the multiple of 2 consideration, as specified by the significance.
• Then, with the help of AutoFill, drag the formula down, since we used relative references, and you will get the following results.

Step 10:

• Consequently, since we specified the significance of 2, the positive numbers are rounded up towards the nearest multiple of 2, which in the case of355 is 2, 1.5 is 2, 7.22 is 8, and 10.01 is 12.
• Also, since we specified a mode of 1, the negative numbers are rounded away from 0 with a multiple of 2 in consideration due to the significance, so -0.331 is rounded away from 0 to -2, –5 to -2, -7.22 to -8. This is emphasized below.

Step 11:

• After that, in cell D16, we want to see what will happen if we specify a significance of 2 and a mode of -1, so in cell D16, we enter the following formula:

Step 12:

• Then, upon pressing Ctrl + Enter, a value of -2, is delivered again, which rounds the negative number -0.331 away from 0 since the mode specified was -1, and -2 is delivered since this is the multiple of 2 considerations as specified by the significance.
• After that, use the Fill Handle to show the result of the other cells in that column.

Step 13:

• Here, we can thus see that specifying either a positive number or a negative number for the mode results in negative numbers being rounded away from 0, and in this case, since significance was 2, the multiple of 2 is considered when rounding.
• Again, in cell E16, we want to see what will happen if we specify a significance of 2 and a mode of 0, so in cell E16, we enter the following formula:

Step 14:

• Finally, upon pressing Ctrl + Enter, the formula will deliver a value of 0, which rounds the negative number toward 0. Dragging the formula down, since we used relative references, delivers the following results.

Consequently, we can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers towards 0 with the multiple of 2 set by the significance taken into consideration, so –0.331 is rounded towards 0, and thus 0 is delivered, -1.5 is rounded towards 0, and thus 0 is delivered, and -7.22 is also rounded towards 0, and thus -6 is delivered when using 0 as the mode, and 2 as the significance.

2. Using FLOOR.MATH Function

In this section, we have a range of numbers and now, we are going to use the FLOOR.MATH function to round down using first a significance of 1 and a mode of 1, then a significance of 1 and a mode of -1, and a significance of 1 and a mode of 0. We are then going to use the FLOOR.MATH function in conjunction with a significance of 2 and a mode of 1, a significance of 2 and a mode of -1, and a significance of 2 and a mode of 0. Go through the following steps for the detailed procedure.

Step 1:

• Firstly, we want to see the effect of a significance of 1 and a mode of 1. So, in cell C6, enter the following formula:

Step 2:

• Secondly, upon pressing Ctrl + Enter, a value of 0, is delivered, which rounds the negative number -0.331 toward 0.
• Then drag the formula down using AutoFill, since we used relative references, and you will get the following results.

Step 3:

• Consequently, since we specified the significance of 1, which is also the default significance, the positive numbers are rounded down towards the nearest integer, which in the case of 355 is 0, 1.5 is 1, 7.22 is 7, and 10.01 is 10.
• Also, since we specified a mode of 1, the negative numbers are rounded toward 0, so -0.331 is rounded toward 0 to 0, -1.5 to -1, and -7.22 to -7. This is emphasized below, and we can compare the difference to the CEILING.MATH treatment of the negative numbers at the significance of 1 and mode of 1.

Step 4:

• Fourthly, in cell D6, we want to see what will happen if we specify a significance of 1, and a mode of -1, so in cell D6, we enter the following formula:

Step 5:

• Afterward, upon pressing Ctrl + Enter, the formula will deliver a value of 0, which rounds the negative number -0.331 towards 0. Dragging the formula down, since we used relative references, delivers the following results.

Step 6:

• Also, specifying either a positive number or a negative number for the mode results in negative numbers being rounded towards 0.
• So, in cell E6, we want to see what will happen if we specify a significance of 1, and a mode of 0, so in cell E6, we enter the following formula:

Step 7:

• Then, upon pressing CTRL+ ENTER, the formula will deliver a value of -1, which rounds the negative number away from 0. Then, drag the formula down, since we used relative references, and deliver the following results.

From here, we can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers away from 0, so –0.331 is rounded away. Similarly, -1 is delivered, -1.5 is rounded away from 0, and thus -2 is delivered, and -7.22 is also rounded away from 0, and thus -8 is delivered when using 0 as the mode. We can also compare the difference in the way the CEILING.MATH function handles the negative numbers with the respective modes.

Step 8:

• Again, we now want to see what will happen if we use a significance of 2 and a mode of 1, so, in cell B16, we enter the following formula:

Step 9:

• Afterward, upon pressing CTRL + ENTER, we will see a value of 0. That rounds the negative number towards 0 since the mode specified was 1, and 0 is delivered since this is the nearest multiple of 2 as specified by the significance.
• Lastly, dragging the formula down, since we used relative references, delivers the following results.

Step 10:

• Here, since we specified the significance of 2, the formula will round down the positive numbers towards the nearest multiple of 2, which in the case of 355 is 0, 1.5 is 0, 7.22 is 6, and 10.01 is 10.
• Also, since we specified a mode of 1, the negative numbers are rounded toward 0 with a multiple of 2 in consideration due to the significance, so -0.331 is rounded towards 0 to 0, -1.5 to 0, and -7.22 to -6. This can be seen below.

Step 11:

• After that, in cell D16, we want to see what will happen if we specify a significance of 2 and a mode of -1, so in cell D16, we enter the following formula:

Step 12:

• After pressing CTRL + ENTER, you will see a value of 0 again, which rounds the negative number -0.331 towards 0, to the nearest multiple of 2 as specified by the significance, which is 0 in this case.
• Then, with the help of Fill Handle, drag the formula down, since we used relative references, and deliver the following results.
• Furthermore, specifying either a positive number or a negative number for the mode results in negative numbers being rounded towards 0, and in this case, since significance was 2, the formula will consider the multiple of 2 while rounding.

Step 13:

• Again, in cell E16, we want to see what will happen if we specify a significance of 2, and a mode of 0, so in cell E16, we enter the following formula:

Step 14:

• Finally, to get the desired result in cell E16, by pressing CTRL +ENTER, you will see a value of -2, which rounds the negative number away from 0.
• Lastly, finish the procedure by dragging the formula down, since we used relative references, and deliver the following results.

Consequently, we can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers away from 0 with the multiple of 2 set by the significance taken into consideration. So, –0.331 is rounded away from 0, and thus -2 is delivered, -1.5 is rounded away from 0, and thus -2 is delivered, and -7.22 is also rounded away from 0, and thus -8 is delivered when using 0 as the mode, and 2 as the significance.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will have a clear concept of the CEILING.MATH and FLOOR.MATH functions in Excel. Please share any further queries or recommendations with us in the comments section below. After that, please give us a moment to solve your issues. We will reply to your queries with the best possible solutions ever.

Related Articles

• All Types of Round Functions in Excel
• 44 Mathematical Functions in Excel

<< Go Back to Excel Functions | Learn Excel