How to Use the CEILING.MATH and the FLOOR.MATH Function in Excel

The CEILING.MATH Function is a new function that was introduced in 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 function provides additional options for rounding negative numbers. The CEILING.MATH Function allows one to reverse the direction of the rounding for negative numbers, by specifying the mode.

Read More: How to Use the CONVERT Function in Excel and creating a BMI Calculator template

Excel CEILING.MATH Function Syntax

The syntax of the CEILING.MATH Function is:

Excel CEILING.MATH Function Syntax

where the ‘number’ is the number of interest, that you would like to be rounded up. This argument is required. The ‘significance’ is optional and specifies the multiple to which the number of interest is to be rounded. The default value for this argument is 1, so if significance is omitted, then 1 is used which means rounding to the nearest integer. The ‘mode’ is an optional argument and controls the way negative numbers are handled. If the mode is 0, or not specified then negative numbers are rounded towards zero, if the ‘mode’ is any other numeric value then numbers are rounded away from zero.

Excel FLOOR.MATH Function Syntax

The FLOOR.MATH Function is a new function that was also introduced 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.

The function also provides additional options for rounding negative numbers.

The FLOOR.MATH Function like the CEILING.MATH Function allows one to reverse the direction of the rounding for negative numbers by specifying the mode.

The syntax of the FLOOR.MATH Function is:

Excel FLOOR.MATH Function Syntax

where the ‘number’ is the number of interest, that you would like to be rounded down. This argument is required. The ‘significance’ is optional and specifies the multiple to which the number of interest is to be rounded down to. The ‘mode’ is an optional argument and controls the way negative numbers are handled. If the mode is 0, or not specified then negative numbers are rounded away from zero, if the ‘mode’ is any other numeric value then numbers are rounded towards zero.

Read More: Top Excel Functions and Features for Management Consultants

Using the CEILING.MATH Function

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, 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, a significance of 2 and a mode of -1, and a significance of 2 and a mode of 0. The source data is shown below.

How to Use CEILING.MATH Function - Image 1

1) In order to get started, we want to see the effect of a significance of 1, and a mode of 1. So in cell B6, enter the following formula:

=CEILING.MATH(A6,1,1)

How to Use CEILING.MATH Function - Image 2

2) Upon pressing CTRL-ENTER, a value of -1, is delivered, which rounds the negative number  -0.331 away from 0.

How to Use CEILING.MATH Function - Image 3

3) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 4

4) Since we specified the significance of 1, which is also the default significance, the positive numbers are rounded up towards the nearest integer, which in the case of 0.355 is 1, 1.5 is 2, 7.22 is 8 and 10.01 is 11. Since we specified a mode of 1, the negative numbers are rounded away from 0, so -0.331 is rounded away from 0 to -1, -1.5 to -2, -7.22 to -8. This is emphasized below.

How to Use CEILING.MATH Function - Image 5

5) Now in cell C6, we want to see what will happen if we specify a significance of 1, and a mode of -1, so in cell C6, we enter the following formula:

=CEILING.MATH(A6,1,-1)

How to Use CEILING.MATH Function - Image 6

6) Upon pressing CTRL-ENTER, a value of -1, is delivered again, which rounds the negative number -0.331 away from 0.

How to Use CEILING.MATH Function - Image 7

7) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 8

8) We can thus see specifying either a positive number or a negative number for the mode results in negative numbers being rounded away from 0.

9) Now in cell D6, we want to see what will happen if we specify a significance of 1, and a mode of 0, so in cell D6, we enter the following formula:

=CEILING.MATH(A6,1,0)

How to Use CEILING.MATH Function - Image 9

10) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number toward 0.

How to Use CEILING.MATH Function - Image 10

11) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 11

12) 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, and -7.22 is also rounded towards 0, and thus -7 is delivered when using 0 as the mode.

13) We now want to see what will happen if we use a significance of 2, and a mode of 1, so in cell H6, we enter the following formula:

=CEILING.MATH(G6,2,1)

How to Use CEILING.MATH Function - Image 12

14) Upon pressing CTRL-ENTER, a value of -2, is delivered, which rounds the negative number away from 0 since the mode specified was 1, and -2 is delivered since this is the multiple of 2 consideration, as specified by the significance.

How to Use CEILING.MATH Function - Image 13

15) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 14

16) Since we specified the significance of 2, the positive numbers are rounded up towards the nearest multiple of 2, which in the case of 0.355 is 2, 1.5 is 2, 7.22 is 8 and 10.01 is 12. 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, -1.5 to -2, -7.22 to -8. This is emphasized below.

How to Use CEILING.MATH Function - Image 15

17) Now in cell I6, we want to see what will happen if we specify a significance of 2, and a mode of -1, so in cell I6, we enter the following formula:

=CEILING.MATH(G6,2,-1)

How to Use CEILING.MATH Function - Image 16

18) 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 consideration as specified by the significance.

How to Use CEILING.MATH Function - Image 17

19) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 18

20) We can thus see 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.

21) Now in cell J6, we want to see what will happen if we specify a significance of 2, and a mode of 0, so in cell J6, we enter the following formula:

=CEILING.MATH(G6,2,0)

How to Use CEILING.MATH Function - Image 19

22) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number toward 0.

How to Use CEILING.MATH Function - Image 20

23) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 21

24) 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.

Read More: How to Use Excel ROUND, ROUNDUP, ROUNDDOWN, MROUND & CEILING Functions

Using the FLOOR.MATH Function

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, 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. The source data is shown below and is the same input numbers as the data, used in the CEILING.MATH example.

How to Use FLOOR.MATH Function - Image 1

1) In order to get started, we want to see the effect of a significance of 1, and a mode of 1. So, in cell B6, enter the following formula:

=FLOOR.MATH(A6,1,1)

How to Use CEILING.MATH Function - Image 2

2) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number -0.331 toward 0.

How to Use CEILING.MATH Function - Image 3

3) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 4

4) 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 0.355 is 0, 1.5 is 1, 7.22 is 7 and 10.01 is 10. 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, -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.

How to Use CEILING.MATH Function - Image 5

5) Now in cell C6, we want to see what will happen if we specify a significance of 1, and a mode of -1, so in cell C6, we enter the following formula:

=FLOOR.MATH(A6,1,-1)

How to Use CEILING.MATH Function - Image 6

6) Upon pressing CTRL-ENTER, a value of 0, is delivered again, which rounds the negative number -0.331 towards 0.

How to Use CEILING.MATH Function - Image 7

7) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 8

8) We can thus see specifying either a positive number or a negative number for the mode results in negative numbers being rounded towards 0.

9) Now in cell D6, we want to see what will happen if we specify a significance of 1, and a mode of 0, so in cell D6, we enter the following formula:

=FLOOR.MATH(A6,1,0)

How to Use CEILING.MATH Function - Image 9

10) Upon pressing CTRL-ENTER, a value of -1, is delivered, which rounds the negative number away from 0.

How to Use CEILING.MATH Function - Image 10

11) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 11

12) 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, and thus -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.

13) We now want to see what will happen if we use a significance of 2, and a mode of 1, so in cell H6, we enter the following formula:

=FLOOR.MATH(G6,2,1)

How to Use CEILING.MATH Function - Image 12

14) Upon pressing CTRL-ENTER, a value of 0, is delivered, which 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.

How to Use CEILING.MATH Function - Image 13

15) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 14

16) Since we specified the significance of 2, the positive numbers are rounded down towards the nearest multiple of 2, which in the case of 0.355 is 0, 1.5 is 0, 7.22 is 6 and 10.01 is 10. 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, -7.22 to -6. This is emphasized below.

How to Use CEILING.MATH Function - Image 15

17) Now in cell I6, we want to see what will happen if we specify a significance of 2, and a mode of -1, so in cell I6, we enter the following formula:

=FLOOR.MATH(G6,2,-1)

How to Use CEILING.MATH Function - Image 16

18) Upon pressing CTRL-ENTER, a value of 0, is delivered 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.

How to Use CEILING.MATH Function - Image 17

19) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 18

20) We can thus see 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 multiple of 2 is considered when rounding.

21) Now in cell J6, we want to see what will happen if we specify a significance of 2, and a mode of 0, so in cell J6, we enter the following formula:

=FLOOR.MATH(G6,2,0)

How to Use CEILING.MATH Function - Image 19

22) Upon pressing CTRL-ENTER, a value of -2, is delivered, which rounds the negative number away from 0.

How to Use CEILING.MATH Function - Image 20

23) Dragging the formula down, since we used relative references, delivers the following results.

How to Use CEILING.MATH Function - Image 21

24) 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.

And there you have it.

Download Excel File

CEILING.MATH-and-FLOOR.MATH-Functions

Conclusion

The CEILING.MATH And FLOOR.MATH Functions were introduced in Excel 2013 and later versions in order to allow specific control over the way negative numbers are rounded. By specifying the mode one can control if negative numbers are rounded towards 0 or away from 0, depending of course on what one’s rounding needs are.

Please feel free to comment and tell us if you use the CEILING.MATH or FLOOR.MATH Functions in your spreadsheets.

Useful Links

CEILING.MATH Function

FLOOR.MATH Function

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.