Home » ExcelBuiltInFunctions » ExcelMathFunctions » ExcelFloorFunction
Excel FLOOR Function
Excel Rounding Functions
The Excel Floor function is one of fifteen rounding functions in Excel.
A full list, along with comparison tables, is provided on the
Excel Rounding Functions page.
Basic Description
The Excel FLOOR function rounds a supplied number towards zero to the nearest multiple of a specified significance.
The syntax of the function is:
FLOOR( number, significance )
Where the arguments are as follows:
number    The number that is to be rounded. 
significance    The multiple of significance that the supplied number is to be rounded to. (This should generally have the same arithmetic sign (positive or negitive) as the supplied number argument). 
Positive and Negative Arguments
Note: In Excel 2003 & 2007, the significance argument must have the same arithmetic sign (positive or negative) as the number argument. However, in later versions of Excel, the Floor function has been improved, so that it can now handle a negative number argument and a positive significance argument. In this case, the Floor function reverses the direction of the rounding (i.e. rounds the supplied number away from zero).Floor Function Examples
In column B of the following spreadsheet, the Excel Floor function is used to round the positive and negative numbers 26.75 and 26.75 to different multiples of significance.
Formulas:  A  B 

1  Number  Floor 

2  26.75  =FLOOR( A2, 0.1 ) 

3  26.75  =FLOOR( A3, 0.5 ) 

4  26.75  =FLOOR( A4, 1 ) 

5  26.75  =FLOOR( A5, 10 ) 

6  26.75  =FLOOR( A6, 20 ) 

7  26.75  =FLOOR( A7, 0.1 ) 

8  26.75  =FLOOR( A8, 1 ) 

9  26.75  =FLOOR( A9, 5 ) 

 Results:  A  B 

1  Number  Floor 

2  26.75  26.7 

3  26.75  26.5 

4  26.75  26 

5  26.75  20 

6  26.75  20 

7  26.75  26.7 

8  26.75  26 

9  26.75  25 


The above examples show how, when the number and the significance arguments have the same arithmetic sign, the Floor function rounds the supplied number towards zero.
Examples with Positive and Negative Arguments
Different combinations of positive and negative arguments, are shown in the spreadsheet below.
The results shown are those that would be obtained in Excel 2010 or Excel 2013.
Formulas:  A  B 

1  Number  Floor 

2  26.75  =FLOOR( A2, 1 ) 

3  26.75  =FLOOR( A3, 1 ) 

4  26.75  =FLOOR( A4, 1 ) 

5  26.75  =FLOOR( A5, 1 ) 

 Results:  A  B  

1  Number  Floor  

2  26.75  26  

3  26.75  #NUM!  

4  26.75  27   returns an error in Excel 2007 & earlier 

5  26.75  26  


This example shows how:
 In Excel 2010 and later versions of Excel, a negative number argument and a positive significance argument reverses the direction of the rounding (i.e. rounds the negative number away from zero) (see cell B4).
 A positive number argument and a negative significance argument still results in an error in all versions of Excel (see cell B3).
Further details of the Excel Floor Function are provided on the Microsoft Office website.
Floor Function Errors
If you get an error from the Excel Floor function, this is likely to be one of the following:
Common Errors
#NUM!    Occurs: 
In Excel 2010 or Excel 2013:    if the supplied number is positive and the supplied significance is negative.  In Excel 2007 or earlier:    if the supplied significance value has a different arithmetic sign to the supplied number argument. 

#DIV/0!    Occurs if the supplied significance argument = 0. 
#VALUE!    Occurs if one or both of the supplied arguments is nonnumeric. 