## 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 | The ‘number’ is the number of interests that you would like to round up. |

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

Mode | Optional | 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.

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

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

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

*0*We will use the following sample dataset for illustration.

**Example 1 – Using CEILING.MATH Function**

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

We want to see the effect of a significance of ** 1** and a mode of

**.**

*1*- In cell
, enter the following formula:*C6*

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

**Step 2:**

- Press
,*Ctrl + Enter*

The formula will deliver a value of ** -1**, which rounds the negative number

**away from**

*-0.331***.**

*0*- Use
**AutoFill**to drag the formula down.

Since we have used relative references. We will get the following results.

**Step 3:**

- 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**,**5**is**2**,**7.22**is**8**, and**10.01**is**11**. - 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:**

In cell ** D6**, we want to see what will happen if we specify a significance of

**, and a mode of**

*1***So,**

*-1.*- In cell D6, enter the following formula:

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

**Step 5:**

- Press
*Ctrl + Enter.*

The formula will deliver a value of ** -1** again, which rounds the negative number

**away from**

*-0.331***.**

*0*- Drag the formula down.

Since we used relative references and we will get the following results.

We can see that specifying either a positive number or a negative number for the mode results in negative numbers and will round the number away from ** 0**.

**Step 6:**

In cell ** E6**, we want to see what will happen if we specify a significance of

**, and a mode of**

*1***.**

*0*- In cell
, enter the following formula:*E6*

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

**Step 7:**

- Press
*Ctrl + Enter.*

A value of ** 0**, which rounds the negative number toward

**.**

*0*- Drag down the
to see the results for the lower cells of the column.*Fill Handle*

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

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

*-7.22***and thus**

*0***is delivered when using**

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

*0***Step 8:**

To find out what will happen if we use a significance of ** 2** and a mode of

*1,*- In cell
, enter the following formula:*C16*

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

**Step 9:**

- Press
*Ctrl + Enter.*

The formula will deliver a value of ** -2**, which rounds the negative number away from

**. Since the mode specified was**

*0***and the formula delivers**

*1***since this is the multiple of**

*-2***considerations, as specified by the significance.**

*2*- Use
to drag the formula down.*AutoFill*

Since we have used relative references, we will get the following results.

**Step 10:**

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

**Step 11:**

We want to see what will happen if we specify a significance of ** 2** and a mode of

*-1.*- In cell
, enter the following formula:*D16*

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

**Step 12:**

- Press
*Ctrl + Enter.*

A value of** -2**, is delivered again, which rounds the negative number

**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*- Use the
to show the result of the other cells in that column.*Fill Handle*

**Step 13:**

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

**, the multiple of**

*2***is considered when rounding.**

*2*We want to see what will happen if we specify a significance of ** 2** and a mode of

**0**.

- In cell
, enter the following formula:*E16*

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

**Step 14:**

- Press
**Ctrl + Enter.**

The formula will deliver a value of ** 0**, which rounds the negative number toward

**.**

*0*- Drag down the formula to get the following results.

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***Example 2 – Using FLOOR.MATH Function**

In this example, we will 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:**

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

- Press
*Ctrl + Enter.*

A value of ** 0**, is delivered, which rounds the negative number

**toward**

*-0.331***.**

*0*- Drag the formula down using
*AutoFill.*

Since we have used relative references, you will get the following results.

**Step 3:**

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

We want to see what will happen if we specify a significance of ** 1**, and a mode of

**.**

*-1*- In cell
, enter the following formula:*D6*

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

**Step 5:**

- Press
*Ctrl + Enter.*

The formula will deliver a value of ** 0**, which rounds the negative number

**towards**

*-0.331***.**

*0*- Drag the formula down to get the following results.

**Step 6:**

- Specifying either a positive number or a negative number for the mode results in negative numbers being rounded towards
.*0* - We want to see what will happen if we specify a significance of 1, and a mode of 0. In cell E6, enter the following formula:

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

**Step 7:**

- Press
*CTRL+ ENTER.*

The formula will deliver a value of **-1**, which rounds the negative number away from ** 0**.

- Drag the formula down to the following results.

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

We will see what will happen if we use a significance of ** 2** and a mode of

**.**

*1*- In cell
**B16**, enter the following formula:

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

**Step 9:**

- Press
*CTRL + ENTER.*

We will see a value of ** 0**. That rounds the negative number towards 0 since the mode specified was

**, and**

*1***is delivered since this is the nearest multiple of**

*0***as specified by the significance.**

*2*- Drag down the formula and we will get the following results.

**Step 10:**

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

We will see what will happen if we specify a significance of ** 2** and a mode of

*-1.*- In cell
, enter the following formula:*D16*

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

**Step 12:**

- Press
*CTRL + ENTER.*

You will see a value of ** 0**, which rounds the negative number

**towards**

*-0.331***, to the nearest multiple of**

*0***as specified by the significance, which is**

*2***in this case.**

*0*- Use
to drag the formula down and we will get the following results.*Fill Handle*

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

**, the formula will consider the multiple of**

*2***while rounding.**

*2***Step 13:**

In cell ** E16**, we want to see what will happen if we specify a significance of

**, and a mode of**

*2*

*0.*- In cell
, we enter the following formula:*E16*

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

**Step 14:**

- Press
*CTRL +ENTER.*

You will see a value of** -2**, which rounds the negative number away from

**.**

*0*- Drag the formula down to get the following results.

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***Download Practice Workbook**

## Related Articles

**<< Go Back to Excel Functions | Learn Excel**