** The CEILING.MATH function** is a new function that we can see in

**Microsoft Excel**2013 and later versions.

**rounds a specified number up to the nearest integer or nearest specified multiple of significance.**

*The CEILING.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**

*The FLOOR.MATH function***and**

*CEILING.MATH***functions in Excel.**

*FLOOR.MATH*## Download Practice Workbook

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

## Excel CEILING.MATH Function: Syntax and Arguments

**Summary:**

rounds a specified number up to the nearest integer or nearest specified multiple of significance.*The CEILING.MATH function*- 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

`=CEILING.MATH(number, [significance], [mode])`

**Arguments:**

Argument | Required or Optional | Value |
---|---|---|

Number | Required | Firstly, the ‘number’ is the number of interests that you would like to round up. |

Significance | Required | Secondly, the ‘significance’ is optional and specifies the multiple to which the number of interests will round up. |

Mode | Optional | Finally, the ‘mode’ is an optional argument and controls the way Excel handles the negative numbers. |

**Return:**

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

## Excel FLOOR.MATH Function: Syntax and Arguments

**Summary:**

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

**Syntax:**

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

`=FLOOR.MATH(number, [significance], [mode])`

**Arguments:**

Argument | Required or Optional | Value |
---|---|---|

Number | Required | The ‘number’ is the number of interests that you would like to round down. |

Significance | Required | The ‘significance’ is optional and specifies the multiple to which the number of interests will round down. |

Mode | Optional | The ‘’ is an optional argument and controls the way Excel handles the negative numbers.mode |

**Return:**

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

## Usage of CEILING.MATH and FLOOR.MATH Functions in Excel

From Excel 2013 and later versions, we can see ** The CEILING.MATH **and

**. 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**

*FLOOR.MATH functions***or away from**

*0***. This depends on what one’s rounding needs are.**

*0*In this section, I will show you how to use** CEILING.MATH **and

*FLOOR.MATH***in Excel. For that, I will use the following data set.**

*functions*### 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

**and a mode of**

*1***. Then, a significance of**

*1***and a mode of**

*1***, and a significance of**

*-1***and a mode of**

*1***. We are then going to use**

*0***in conjunction with a significance of**

*the CEILING.MATH function***and a mode of**

*2***. Then, a significance of**

*1***and a mode of**

*2***, and a significance of**

*-1***and a mode of**

*2***. The detailed steps are as follows.**

*0***Step 1:**

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

`=CEILING.MATH(B6,1,1)`

**Step 2:**

- Secondly, upon pressing
, the formula will deliver a value of*Ctrl + Enter*, which rounds the negative number*-1*away from*-0.331*.*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
, which is also the default significance, the formula will round the positive numbers towards the nearest integer. That, in the case of*1*is*355***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
. So the formula will round away*0*from*-0.331*to*0*,*-1*to*-1.5*, and*-2*to*-7.22*. You can see it in the following image.*-8*

**Step 4:**

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

`=CEILING.MATH(B6,1,-1)`

**Step 5:**

- Afterwards, pressing
, the formula will deliver a value of*Ctrl + Enter*again, which rounds the negative number*-1*away from*-0.331*.*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
, we want to see what will happen if we specify a significance of*E6*, and a mode of*1*. So, in cell*0*, we enter the following formula:*E6*

`=CEILING.MATH(B6,1,0)`

**Step 7:**

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

We can now see in comparison to a mode of ** -1**, and

**, a mode of**

*1***rounds the negative numbers towards**

*0***. So,**

*0***is rounded towards**

*–0.331***, and thus**

*0***is delivered,**

*0***is rounded towards**

*-1.5***, and thus**

*0***is delivered. Also,**

*-1***is also rounded towards**

*-7.22***, and thus**

*0***is delivered when using**

*-7***as the mode.**

*0***Step 8:**

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

`=CEILING.MATH(B16,2,1)`

**Step 9:**

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

**Step 10:**

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

**Step 11:**

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

`=CEILING.MATH(B16,2,-1)`

**Step 12:**

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

**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
, and in this case, since significance was*0*, the multiple of*2*is considered when rounding.*2* - Again, in cell
, we want to see what will happen if we specify a significance of*E16*and a mode of*2*, so in cell*0*, we enter the following formula:*E16*

`=CEILING.MATH(B16,2,0)`

**Step 14:**

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

Consequently, we can now see in comparison to a mode of ** -1**, and

**, a mode of**

*1***rounds the negative numbers towards**

*0***with the multiple of**

*0***set by the significance taken into consideration, so**

*2***is rounded towards**

*–0.331***, and thus**

*0***is delivered,**

*0***is rounded towards**

*-1.5***, and thus**

*0***is delivered, and**

*0***is also rounded towards**

*-7.22***, and thus**

*0***is delivered when using**

*-6***as the mode, and**

*0***as the significance.**

*2*### 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

**and a mode of**

*1***then a significance of**

*1,***and a mode of**

*1***, and a significance of**

*-1***and a mode of**

*1***. We are then going to use**

*0*

*the***in conjunction with a significance of**

*FLOOR.MATH function***and a mode of**

*2***, a significance of**

*1***and a mode of**

*2***, and a significance of**

*-1***and a mode of**

*2***. Go through the following steps for the detailed procedure.**

*0***Step 1:**

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

`=FLOOR.MATH(B6,1,1)`

**Step 2:**

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

**Step 3:**

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

**Step 4:**

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

`=FLOOR.MATH(B6,1,-1)`

**Step 5:**

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

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

`=FLOOR.MATH(B6,1,0)`

**Step 7:**

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

From here, we can now see in comparison to a mode of ** -1**, and

**, a mode of**

*1***rounds the negative numbers away from**

*0***, so**

*0***is rounded away. Similarly,**

*–0.331***is delivered,**

*-1***is rounded away from**

*-1.5***, and thus**

*0***is delivered, and**

*-2***is also rounded away from**

*-7.22***, and thus**

*0***is delivered when using**

*-8***as the mode. We can also compare the difference in the way**

*0*

*the***handles the negative numbers with the respective modes.**

*CEILING.MATH function***Step 8:**

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

`=FLOOR.MATH(B16,2,1)`

**Step 9:**

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

**Step 10:**

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

**Step 11:**

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

`=FLOOR.MATH(B16,2,-1)`

**Step 12:**

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

**Step 13:**

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

`=FLOOR.MATH(B16,2,0)`

**Step 14:**

- Finally, to get the desired result in cell
, by pressing*E16*, you will see a value of*CTRL +ENTER*, which rounds the negative number away from*-2*.*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

**, a mode of**

*1***rounds the negative numbers away from**

*0***with the multiple of**

*0***set by the significance taken into consideration. So,**

*2***is rounded away from**

*–0.331***, and thus**

*0***is delivered,**

*-2***is rounded away from**

*-1.5***, and thus**

*0***is delivered, and**

*-2***is also rounded away from**

*-7.22***, and thus**

*0***is delivered when using**

*-8***as the mode, and**

*0***as the significance.**

*2**Notes :*

*There is a slight difference between***the CEILING.MATH function**and**the CEILING.PRECISE****function**in Excel. That is in the syntax of the following function, you don’t need mode for showing value.*Unlike the two functions described in this article,***the CEILING function**and**the FLOOR function**are available in**Excel 2007**.

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

**functions in Excel. Please share any further queries or recommendations with us in the comments section below.**

*FLOOR.MATH*The** ExcelDemy** team is always concerned about your preferences. Therefore, after commenting, please give us a moment to solve your issues. We will reply to your queries with the best possible solutions ever.