# How to Use FLOOR Function in Excel (11 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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

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)` ### 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. ### 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. ### 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. ### 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. ### 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.

## Related Articles #### Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  