FLOOR Function in Excel (Table of Contents)
FLOOR in Excel
- FLOOR Function is rounding a number down to the nearest multiple of significance value which you specify.
- FLOOR function is a worksheet function categorized under Math/Trigonometry function.
- It can be entered as part of a formula in a cell of a worksheet.
- Mathematical definitions of the “floor” function are defined as or described as per the given input, floor always moves in the direction of negative infinity (down), floor returns the largest integer that is smaller than or equal to the input value.
- Currently FLOOR.MATH function was added under Math/Trigonometry function in Excel 2013 to replace this FLOOR function, Floor function is still available for backward compatibility in excel (under the insert function button (fx)).
- It is most commonly used function to set pricing after currency conversions & discounts on products.
FLOOR Formula in Excel
Below is the FLOOR Formula in Excel :
Explanation of FLOOR Function in Excel
The FLOOR formula in excel has two arguments:
- Number: It is a number you want to round or number that we want to round.
- Significance: It is a multiple to which you wish to round the number.
How to Use the FLOOR Function in Excel?
FLOOR function in Excel is very simple and easy to use. Let understand the working of FLOOR function in Excel by some FLOOR Formula example.
Example #1
In this FLOOR function in Excel example, I have to sort out only bundles which are complete i.e. bundles having only 5 quantities of a product.
The quantities are as mentioned in column B, Now, I want to bundle them in multiples of 5 using floor function in column C.
Need to Follow below-mentioned steps in Excel:
Select the output cell where we need to find out floor value i.e. C8 in this example.
Click the insert function button (fx) under formula toolbar, a dialog box will appear. Type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on FLOOR function.
A dialog box appears where arguments (Number & significance) for floor function needs to be filled or entered.
Here number argument is 14, Whereas significance value is 5, the Significance value is common to other cells also.
Following FLOOR formula is applied in the C8 cell, i.e. =FLOOR(B8,5).
Output or Result: The value 14 is rounded to 10.
Similarly, it is applied to other cells in that column to get the desired output.
Example #2 – FLOOR Function For Decimal Values
Here, In the second example of FLOOR function in Excel, the number argument is decimal value in cell B17 & significance value is 2 in cell C17. I need to find out the nearest multiple of 2 for the Decimal value 5.7
Need to Follow below-mentioned steps in Excel:
Select the output cell where we need to find out floor value i.e. D17 in this example.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on FLOOR function.
A dialog box appears where arguments (Number & significance) for floor function needs to be filled.
Here number argument is 5.7, Whereas significance value is 2. Following floor formula is applied in the D17 cell, i.e. =FLOOR(B17,C17)
Output or Result: It Rounds 5.7 down to nearest multiple of 2 and it returns the value 4.
Example #3 – FLOOR Function For Negative Arguments
In the third example of FLOOR function in Excel, the number argument is a negative value (-4.5) in cell B22 & significance value is also negative value (-2) in cell C22. I need to find out nearest multiple of -2 for the number value -4.5.
Select the output cell where we need to find out floor value i.e. D22 in this example.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on FLOOR function.
A dialog box appears where arguments (Number & significance) for floor function needs to be filled.
Here number argument is -4.5, Whereas significance value is -2, Both of them are negative values.
Following FLOOR formula is applied in a D22 cell, i.e. =FLOOR(B22,C22).
Output or Result: It Rounds -4.5 down to nearest multiple of -2 and it returns the value -4.
Example #4 – FLOOR Function When Any One of the Argument is Negative Value
FLOOR function in Excel returns an #NUM! error value if the number value is positive and the significance value is negative.
In the below-mentioned example, the number argument is a positive value (5.7) in cell F9 & significance value is a negative value (-2) in cell G9. I need to find out nearest multiple of -2 for the value 5.7.
Select the output cell where we need to find out floor value i.e. H9 in this example.
Click the insert function button (fx) under formula toolbar, a dialog box will appear, Type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on floor function.
A dialog box appears where arguments (Number & significance) for floor function needs to be filled.
Here number argument is 5.7, Whereas significance value is -2
Following FLOOR function is applied in an H9 cell, i.e. =FLOOR(F9,G9).
Output or Result: FLOOR function returns an #NUM! error value if the number is positive value and significance is a negative value.
Whereas in case, if the number is negative and significance is a positive value.
In current versions of Excel 2016 & 2010 or later, it can handle a negative number and positive significance. #NUM! error value will not occur.
In the below-mentioned example, a negative number argument (-23.75) in cell F10 and a positive significance argument (1) in cell G10.
FLOOR Function in excel reverses the negative number away from zero i.e. it returns an output value as -24 (More negative value) in cell H10.
Things to remember about the FLOOR Function in Excel
The FLOOR Function in excel performs rounding based on the below mentioned or following rules:
- If number argument is equal to an exact multiple of significance, no rounding occurs.
The Output will be :
- If either argument ((number, significance) is non-numeric (Text value or string), FLOOR function returns an #VALUE! error.
The Output will be :
- If “number” or “significance” argument have different signs (e.g. #, %,?), FLOOR function returns an #VALUE! error.
The Output will be :
- The significance & number argument must have the same arithmetic sign (positive or negative), otherwise, if they have different arithmetic signs.
The Floor function returns or results in an #NUM error value.
- Exceptional case or scenario, In current versions of Excel 2016 or (2010 and later), a negative number argument (-23.75) in cell F10 and a positive significance argument (1) in cell G10 Floor function reverses the negative number away from zero i.e. -24 in cell H10, if you try this it in Excel 2007 & earlier versions, it returns a value error (#NUM! error value)
The Output will be :
- If the number argument is a positive value and significance argument is “0” or ZERO, it returns #DIV/0! Error.
The Output will be :
Recommended Articles
This has been a guide to FLOOR in Excel. Here we discuss the FLOOR Formula in Excel and How to use the FLOOR Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
