Excel FLOOR Function

Using the Excel FLOOR Function


The Excel FLOOR function is used to round numbers down towards zero to the nearest user specified multiple of significance. The FLOOR function will always round down. If you are trying to round numbers up, away from zero, the CEILING function can be used. For more control over how FLOOR handles negative numbers, see FLOOR.MATH.


=FLOOR (number, significance)

Syntax Breakdown

The number argument is required and is the number that you would like to round down towards zero.

The significance argument is also required and is the multiple that will be used when rounding down towards zero.

Usage Notes

The FLOOR function is useful when you have a need to round numbers down to a certain level of significance. For example, if you would like to round a purchase total down to the nearest dollar, you could accomplish this using FLOOR (A1, 1), assuming that A1 contains the purchase total.

When number is positive it will be rounded down (towards zero). If number is negative the value will be rounded down (away from zero). When number is an exact multiple of significance then no rounding will occur.

FLOOR Errors
If the FLOOR function receives either argument (number or significance) as a non-numerical value, the #VALUE! error is returned.

The function will return the #NUM! error if number is positive and significance is negative.

Excel FLOOR NUM Error

FLOOR and FLOOR.MATH have the following differences:
  • FLOOR.MATH provides more control over how negative numbers are rounded. You can specify if they round toward or away from zero.
  • FLOOR.MATH defaults to a significance of 1, thus rounding to the nearest integer.
  • Changing the sign of the significance value in FLOOR.MATH has no effect on the results.