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.

Excel FLOOR Function

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)

Excel FLOOR Function

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

Excel FLOOR Function

Excel FLOOR Function


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.

Excel FLOOR Function

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

positive number + positive significance

➤Press ENTER
➤Drag down the Fill Handle Tool

positive number + positive significance

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

positive number + positive significance

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

=FLOOR(2341.76,100)

Excel FLOOR Function

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.

Positive number + negative significance

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

Positive number + negative significance

➤Press ENTER
➤Drag down the Fill Handle Tool

Positive number + negative significance

Result:
Because of using the negative significance values for positive prices, we are getting #NUM! Error here.

Positive number + negative significance

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.

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

negative number + positive significance

➤Press ENTER
➤Drag down the Fill Handle Tool

negative number + positive significance

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.

Excel FLOOR Function

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.

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

negative number + negative significance

➤Press ENTER
➤Drag down the Fill Handle Tool

negative number + negative significance

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.

negative number + negative significance

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.

Excel FLOOR Function

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

fraction significance

➤Press ENTER
➤Drag down the Fill Handle Tool

fraction significance

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.

fraction significance

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


Similar Readings


6. For Zero Significance

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

Excel FLOOR Function

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

zero significance

➤Press ENTER
➤Drag down the Fill Handle Tool

zero significance

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.

Excel FLOOR Function


7. For Same Number and Significance

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

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

same number & significance

➤Press ENTER
➤Drag down the Fill Handle Tool

same number & significance

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.

Excel FLOOR Function


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.

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

non-numeric significance

➤Press ENTER
➤Drag down the Fill Handle Tool

non-numeric significance

Result:

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

non-numeric significance


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.

Excel FLOOR Function

➤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

formula

➤Press ENTER
➤Drag down the Fill Handle Tool

formula

Result:

In this way, you will get rounded discount prices.

formula


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.

Excel FLOOR Function

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

time

➤Press ENTER
➤Drag down the Fill Handle Tool

time

Result:

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

Excel FLOOR Function


11. Using FLOOR Function in a VBA Code

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

VBA

➤Go to Developer Tab>>Visual Basic Option

VBA

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

VBA

After that, a Module will be created.

VBA

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

VBA

➤Press F5

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

Excel FLOOR Function


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.

practice


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

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

Leave a reply

ExcelDemy
Logo