Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .