The **FLOOR function **in Excel rounds down both the integer number and decimal number to the nearest specified multiple of significance. In this article, you will get to know the introduction and usage of the **FLOOR function **in Excel.

**Table of Contents**hide

## Download Workbook

## FLOOR Function: Syntax & Arguments

**⦿**** Function Objective**

The **FLOOR function **rounds a number down to the nearest multiple of significance.

**⦿**** Syntax**

`FLOOR(number, significance)`

**⦿**** Arguments**

Argument |
Required/Optional |
Explanation |
---|---|---|

number | Required | The number to round up. |

significance | Required | The multiple to which the number should be rounded. |

**⦿**** Return Value**

The **FLOOR function **returns a rounded number.

**⦿**** Version**

The **FLOOR function **has been introduced in **Excel 2003 **version and is available for all versions after that.

## 11 Examples of Using The FLOOR Function in Excel

Here, we have used the following two tables for demonstrating the applications of the **FLOOR function **in Excel.

For creating the article, we have used *Microsoft Excel 365* version, you can use any other versions according to your convenience.

### 1. Using FLOOR Function for Positive Number and Positive Integer Significance

For rounding the prices of the **Price column **you can use the **FLOOR function **and for having the rounded numbers we have added the **Rounded Price column**.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5,100)`

Here, **C5 **is the price which we want to round down and **100 **is the **significance**. **FLOOR **will round down the value in **C5 **to the nearest multiple of **100**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

In this way, you will be able to round down the prices to the nearest multiple of

**100**.

Similarly, you can get the result by inserting direct input instead of reference like below.

`=FLOOR(2341.76,100)`

**Read More:** **51 Mostly Used Math and Trig Functions in Excel**

### 2. For Positive Number and Negative Integer Significance

Here, we will use positive price values and negative integer significance for rounding down the prices.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5,-1000)`

Here, **C5 **is the price which we want to round down and –**1000 **is the **significance**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Because of using the negative significance values for positive prices, we are getting

**#NUM!**Error here.

**Read More:** **44 Mathematical Functions in Excel (Download Free PDF)**

### 3. Using FLOOR Function for Negative Number and Positive Integer Significance

You can round down the negative temperatures by using the positive significance values in the **FLOOR function**.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5,5)`

Here, **C5 **is the temperature which we want to round down and **5 **is the **significance**. **FLOOR **will round down the value in **C5 **to the nearest multiple of **5**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Then, you will be able to round down the temperatures to the nearest multiple of

**5**. Here, we can see that because of using positive significance values with negative temperatures the numbers are rounded away from zero or rounded to a lower value.

**Read More:** **How to Use ROUNDDOWN Function in Excel (5 Methods)**

### 4. FLOOR Function for Negative Number and Negative Integer Significance

We will round up the negative temperatures by using the negative significance values in the **FLOOR function**.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5,-5)`

Here, **C5 **is the negative temperature that we want to round up and –**5 **is the **significance**. **FLOOR **will round up the value in **C5 **to the nearest multiple of **5**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

After that, you will be able to round up the temperatures to the nearest multiple of

**5**. Here, we can see that because of using negative significance values with negative temperatures the numbers are rounded toward zero or rounded to a higher value.

**Read More:** **How to Use ROUND Function in Excel (With 9 Examples)**

### 5. For Fraction Significance

You can use the fraction significance values for rounding down the prices.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5,0.5)`

Here, **C5 **is the price which we want to round down and **0.5 **is the **significance**. **FLOOR **will round down the value in **C5 **to the nearest multiple of **0.5**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Then, you will be able to round down the prices to the nearest multiple of

**0.5**. Here, we can see that because of using fraction significance values with prices the numbers are not rounded properly as they remain as decimal numbers.

**Read More:** **How to Use ROUNDUP Function in Excel (6 Examples)**

**Similar Readings**

**How to Use MMULT Function in Excel (6 Examples)****VBA EXP Function in Excel (5 Examples)****How to Use TRUNC Function in Excel (4 Examples)****Use TAN Function in Excel (6 Examples)****How to Use Excel PI Function (7 Examples)**

### 6. For Zero Significance

Here, we will use a significance in the **FLOOR function **as zero for rounding down the prices.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5,0)`

Here, **C5 **is the price which we want to round down and **0 **is the **significance**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Because of using the zero significance values, we are getting

**#DIV/0!**Error here as how many times you multiply zero it always remains zero.

### 7. For Same Number and Significance

Here, we will use the same prices and same significance values for rounding the prices.

➤Select the output cell **E5**➤Type the following formula

`=FLOOR(C5,D5)`

Here, **C5 **is the price which we want to round down and **D5 **is the **significance**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

We can see that because of using the same prices and significance values the prices are not rounded rather they are remaining the same as before.

### 8. For Non-numeric Significance

We have the non-numeric values in the **Significance **column which we will use as the significance values in the **FLOOR function**.

➤Select the output cell **E5**➤Type the following formula

`=FLOOR(C5,D5)`

Here, **C5 **is the price which we want to round down and **D5 **is the **significance **which is in text format.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

Because of using the non-numeric significance values, we are getting **#VALUE! **Error here.

### 9. Using The FLOOR Function in a Formula

We will calculate the discount price by multiplying prices with discounts and then we will round down them by using the **FLOOR function** here.

➤Select the output cell **E5**➤Type the following formula

`=FLOOR(C5*D5,5)`

Here, **C5 **is the price and **D5 **is the discount.

**(C5*D5)→**It will multiply the price with the discount.**Output→117.09**

**FLOOR((C5*D5),5)**becomes**FLOOR(117.09,5)→****FLOOR**will round down the value**117.09**to the nearest multiple of**5**.**Output→115**

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

In this way, you will get rounded discount prices.

### 10. Using FLOOR Function for Time

Here, we will round down the times or omit the minutes from the order times and only get the hour of the order times.

➤Select the output cell **D5**➤Type the following formula

`=FLOOR(C5, “1:00”)`

Here, **C5 **is the order time which we want to round down and **“1:00” **is the **significance**. **FLOOR **will round down the value in **C5 **to the nearest multiple of **1:00**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool

** Result**:

In this way, you will be able to round down the order times to the nearest multiple of **1:00 **or one hour.

### 11. Using FLOOR Function in a VBA Code

You can use the **FLOOR function **in the **VBA **code also.** **

➤Go to **Developer **Tab>>**Visual Basic **Option

Then, the **Visual Basic Editor **will open up.

➤Go to **Insert **Tab>> **Module **Option

After that, a **Module** will be created.

➤Write the following code

```
Sub floornumber()
Range("D5") = Application.WorksheetFunction.Floor((Range("C5")), 1000)
Range("D6") = Application.WorksheetFunction.Floor((Range("C6")), 1000)
Range("D7") = Application.WorksheetFunction.Floor((Range("C7")), 1000)
Range("D8") = Application.WorksheetFunction.Floor((Range("C8")), 1000)
Range("D9") = Application.WorksheetFunction.Floor((Range("C9")), 1000)
Range("D10") = Application.WorksheetFunction.Floor((Range("C10")), 1000)
Range("D11") = Application.WorksheetFunction.Floor((Range("C11")), 1000)
End Sub
```

**FLOOR **will round down the values of the cells **C5 **to **C11 **of **Column C **to the nearest multiple of **1000**. and we will get the outputs in corresponding cells of **Column D**.

➤Press **F5**

** Result**:

In this way, you will be able to round down the prices to the nearest multiple of

**1000**.

## Things to Notice

🔺 When the number itself is negative, then you can only use negative **Significance**

🔺The **FLOOR function** works with only the numeric values

🔺 For using zero as a Significance, this function will give an error

🔺 If the **FLOOR function **has the same values for the two arguments then no rounding will occur

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, we tried to cover the introduction and usage of the **FLOOR function **in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.