How to Use FLOOR Function in Excel (11 Examples)

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.

FLOOR Function: Syntax & Arguments

⦿ Function Objective

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

⦿ Syntax

FLOOR(number, significance)

⦿ Arguments

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

How to Use The FLOOR Function in Excel: 11 Examples

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

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.

Read More: How to Use ROUND Function 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

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: All Types of Round Functions in Excel

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

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.

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.

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 ROUNDUP Function in Excel

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

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 ROUNDDOWN Function in Excel

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

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

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

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

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

➤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

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.

Download Workbook

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.

Related Articles

• How to Use CEILING Function in Excel
• How to Use RAND Function in Excel
• How to Use RANDBETWEEN Function in Excel
• How to Use TRUNC Function in Excel
• How to Use CEILING.MATH and FLOOR.MATH Functions in Excel
• How to Use INT Function in Excel

<< Go Back to Excel Functions | Learn Excel