FLOOR function
FLOOR rounds a number down to the nearest multiple you choose, such as the earlier 5-minute mark. It still works, but FLOOR.MATH supersedes it for new workbooks.
Example
Read it as a sentence: round 2.7 down to the nearest multiple of 0.5, which gives 2.5.
A
B
C
D
1
2.5
2
3
Syntax
FLOOR(number, significance)
-
number(required) - Value to round.
-
significance(required) - The multiple to round down to.
Returns: number rounded down to the nearest multiple of significance.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=FLOOR(2.3,0.5) | 2 | Positive |
=FLOOR(2.7,0.5) | 2.5 | Positive 2.7 |
=FLOOR(-2.7,0.5) | -3 | Negative number, positive significance |
=FLOOR(-2.7,-0.5) | -2.5 | Both negative |
=FLOOR(0,0.5) | 0 | Number 0 |
=FLOOR("2.7",1) | 2 | Text-number |
=FLOOR(2.7,-0.5) | #NUM! | Positive number, negative significance |
=FLOOR(2.7,0) | #DIV/0! | Significance 0 |
Watch out for
- Superseded by FLOOR.MATH; use that in new workbooks.
- A positive number with a negative significance returns #NUM!.
- Zero significance returns #DIV/0!.
Where it works
- Excel
- FLOOR is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, but behaves differently. Sheets makes the factor optional and treats sign combinations a little differently.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .