Taryn N
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.
Excel CEILING.MATH Function Syntax
The syntax of the CEILING.MATH Function is:
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 interests 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:
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 interests is to be rounded down. 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.
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.
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)
2) Upon pressing CTRL-ENTER, a value of -1, is delivered, which rounds the negative number -0.331 away from 0.
3) Dragging the formula down, since we used relative references, delivers the following results.
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.
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)
6) Upon pressing CTRL-ENTER, a value of -1, is delivered again, which rounds the negative number -0.331 away from 0.
7) Dragging the formula down, since we used relative references, delivers the following results.
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)
10) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number toward 0.
11) Dragging the formula down, since we used relative references, delivers the following results.
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)
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.
15) Dragging the formula down, since we used relative references, delivers the following results.
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.
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)
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.
19) Dragging the formula down, since we used relative references, delivers the following results.
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)
22) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number toward 0.
23) Dragging the formula down, since we used relative references, delivers the following results.
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.
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.
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)
2) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number -0.331 toward 0.
3) Dragging the formula down, since we used relative references, delivers the following results.
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.
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)
6) Upon pressing CTRL-ENTER, a value of 0, is delivered again, which rounds the negative number -0.331 towards 0.
7) Dragging the formula down, since we used relative references, delivers the following results.
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)
10) Upon pressing CTRL-ENTER, a value of -1, is delivered, which rounds the negative number away from 0.
11) Dragging the formula down, since we used relative references, delivers the following results.
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)
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.
15) Dragging the formula down, since we used relative references, delivers the following results.
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.
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)
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.
19) Dragging the formula down, since we used relative references, delivers the following results.
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)
22) Upon pressing CTRL-ENTER, a value of -2, is delivered, which rounds the negative number away from 0.
23) Dragging the formula down, since we used relative references, delivers the following results.
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.
