FLOOR
Rounds a number down to the nearest multiple of significance.
FLOOR(number, significance)Arguments
numberThe number to roundsignificanceThe multiple to round to
=FLOOR(23,5)Rounds down to nearest 5
=FLOOR(2.5,1)Rounds down to integer
=FLOOR(Time,"0:15")Rounds to 15-minute interval
- •Use FLOOR.MATH for more options
- •Significance must match number sign
- •Useful for time and price rounding
- •Forgetting that FLOOR requires a significance argument with the same sign as the number - mixing signs (a positive number with a negative significance, for example) raises a #NUM! error
- •Assuming FLOOR always rounds toward zero - for negative numbers, FLOOR actually rounds away from zero (more negative), which trips up anyone expecting truncation-like behavior
- •Using the legacy FLOOR when FLOOR.MATH or FLOOR.PRECISE would handle mixed-sign arguments more gracefully - the newer functions loosen FLOOR's stricter sign-matching requirement
CEILINGRounds up to the nearest multiple of significance instead of down, the mirror function to FLOOR.MROUNDRounds to the nearest multiple rather than always rounding down, offering a middle ground between FLOOR and CEILING.FLOOR.MATHThe modern replacement for FLOOR, with more flexible handling of negative numbers and significance values.Why does FLOOR return a #NUM! error?
The number and significance arguments likely have mismatched signs - a positive number needs a positive significance, and a negative number needs a negative significance.
Does FLOOR always round toward zero?
No - for positive numbers it rounds down toward zero, but for negative numbers it rounds down away from zero (more negative), which can be counterintuitive.
Should I use FLOOR or FLOOR.MATH?
FLOOR.MATH is the modern version and handles negative numbers more flexibly without raising errors - FLOOR is kept mainly for compatibility with older files.
Need to translate a formula using FLOOR?
Use our translator to convert your complete formula