Math & Rounding
Sign-Independent Directed Rounding Toward +Infinity and -Infinity (CEILING.PRECISE & FLOOR.PRECISE)
Round positive and negative numbers strictly toward +infinity or -infinity using ABS(significance) and INT identities that never fail with #NUM! when value and step have opposite signs.
Why legacy CEILING and FLOOR break or flip direction on negative values
Legacy CEILING(number, significance) and FLOOR(number, significance) depend on the sign of significance and throw #NUM! in classic Excel whenever a positive number is paired with a negative step. Worse, rounding "away from zero" vs "toward +infinity" diverges on negative numbers: -4.3 rounded upward toward +infinity at step 2 lands on -4, whereas rounding away from zero lands on -6.
CEILING.PRECISE(x, s) and FLOOR.PRECISE(x, s) ignore the sign of s by taking ABS(s) and always round mathematically along the real number line: CEILING.PRECISE moves right toward +infinity (-INT(-x / ABS(s)) * ABS(s)), while FLOOR.PRECISE moves left toward -infinity (INT(x / ABS(s)) * ABS(s)).
| Input x | Step s | Toward +Infinity (CEILING.PRECISE) | Toward -Infinity (FLOOR.PRECISE) |
|---|---|---|---|
| 4.3 | 2 or -2 | 6 | 4 |
| -4.3 | 2 or -2 | -4 | -6 |
| -6.0 | 2 | -6 | -6 |
| 4.3 | 0 | 0 | 0 |
Portable CEILING.PRECISE Formula (Toward +Infinity)
Negate x before INT, negate the integer result back, and multiply by ABS(significance), returning 0 cleanly when significance equals 0.
=IF(B2 = 0, 0, -INT(-A2 / ABS(B2)) * ABS(B2))Portable FLOOR.PRECISE Formula (Toward -Infinity)
Because Excel and Arte Sheets INT always floors toward -infinity, dividing by ABS(significance) inside INT implements FLOOR.PRECISE directly across all real inputs.
=IF(B2 = 0, 0, INT(A2 / ABS(B2)) * ABS(B2))Try the example
Use the original values or change them to test your understanding.