Round a number down to the next multiple
=FLOOR(number, significance)
FLOOR is useful for rounding numbers down to a specified multiple, for example cents from the next dollar.
FLOOR defines 'the number to round' and the 'multiple to round down to'.
FLOOR and numbers.
In the examples below, the FLOOR function is used to round GDP per capita figures down to the next multiple (whether 100, 10 or 5).
Use the FLOOR function to round a number down to the next multiple.
Syntax
=FLOOR(number, significance)
The FLOOR function consists of 2 arguments:
- number. Required. The value to be rounded down.
- significance. Required. The multiple to be rounded down to.
Try it now!
- Enter your formulas into the grey cells.
Scope
FLOOR defines 'the number to round' and the 'multiple to round down to'.
|
=FLOOR(16726,100) || result is 16700. The number 16726 is rounded down to the next 100. =FLOOR(16726,25) || result is 16700. The number 16726 is rounded down to the next multiple of 25. |
|
=FLOOR(944-963,-5) || result is -15. The result of 944 less 963 (-19) is rounded down to nearest multiple of -5 (-15). If the multiple had been positive 5, the result would have been -20. =FLOOR(944.41-963.49,-0.1) || result is -19. The result of 944.41 less 963.49 (-19.08) is rounded down to nearest multiple of 0.1 (-19). |
|
=FLOOR(E7,3) || result is the value from cell E7 rounded down to the nearest multiple of 3. If cell E7 contains a 4, result is 3. If cell E7 contains a 3, result is 3. If cell E7 contains a non-numeric value, #VALUE! is returned. |
|
=FLOOR("12:01:00 PM","0:15") || result is 12:00:00 PM, with the time being rounded down to the nearest 15 minutes. |
Caveats
- FLOOR is a compatibility function, replaced by FLOOR.MATH and FLOOR.PRECISE.
- FLOOR requires a numeric value. Non-numeric values will return #VALUE!.
- If number is an exact multiple of significance, no rounding occurs.
- If number and significance are positive, number is rounded down towards zero.
- If number and significance are negative, number is rounded up towards zero.
- If number is negative and significance is positive, number is rounded down away from zero.
- If number is positive and significance is negative, #NUM! is returned.
Common issues
- #VALUE!: number is not a numeric value.
- #NUM!: number is positive and significance is negative.
Related Functions
- CEILING: round up to the next multiple.
- MROUND: round to the nearest multiple.
- ROUND: round normally to a specified number of digits.
- ROUNDDOWN: round down towards zero.
- ROUNDUP: round up away from zero.
Functions Category