Math
Round Up or Down to Next Half (0.5), Quarter (0.25) or Custom Multiple
Round a numeric value upward, downward or to the nearest positive increment, with explicit input checks.
Snap decimal quantities to 0.5-hour blocks, 0.25 increments, or pack multiples!
Use CEILING.MATH for rounding toward positive infinity and FLOOR.MATH toward negative infinity with their default mode. Thus -2.15 rounded upward to a half is -2, while downward is -2.5. Exact multiples remain unchanged.
For nearest rounding use ROUND(value/increment,0)*increment; halfway cases round away from zero. These are different policies: choose the rule required by the task rather than treating all of them as billing rules.
This example requires a numeric value and a strictly positive numeric increment. It does not infer time units: 2.15 means decimal hours here, not 2 hours 15 minutes. Decimal floating-point arithmetic may leave tiny residues; these formulas do not establish a legal currency or measurement precision policy.
| Rounding Goal (Input = 2.15) | Formula (Multiple B3 = 0.5) | Output (0.5 Step) | Output (0.25 Step) |
|---|---|---|---|
| Round UP to next increment | CEILING.MATH(B2, B3) | 2.50 | 2.25 |
| Round DOWN to completed increment | FLOOR.MATH(B2, B3) | 2.00 | 2.00 |
| Round to NEAREST increment | ROUND(B2 / B3, 0) * B3 | 2.00 | 2.25 |
Round Up to Next Half (0.5) or Custom Multiple (CEILING.MATH)
Round toward positive infinity using the positive increment in B3. Negative values move toward zero.
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER(B3))),"Invalid inputs",IF(B3<=0,"Invalid inputs",CEILING.MATH(B2, B3)))Round down to a multiple
Round toward negative infinity. For negative values this moves away from zero; it is not truncation.
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER(B3))),"Invalid inputs",IF(B3<=0,"Invalid inputs",FLOOR.MATH(B2, B3)))Round to the nearest multiple
Divide by the increment, round the quotient to an integer, then multiply back. Exact halfway cases round away from zero.
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER(B3))),"Invalid inputs",IF(B3<=0,"Invalid inputs",ROUND(B2/B3,0)*B3))Try the example
Use the original values or change them to test your understanding.