Math & Rounding
Round to Nearest Multiple (MROUND Pattern) vs. Up/Down Multiples (CEILING & FLOOR)
Round quantities, billing increments, and unit prices to the nearest, next-higher, or next-lower multiple of 5, 0.25, or carton pack size using ROUND, CEILING, and FLOOR.
Choosing between nearest, upward, and downward multiple rounding
Dividing a value by step size M, rounding to 0 decimals with ROUND(Value / M, 0), and multiplying back by M rounds to the nearest multiple of M—reproducing MROUND(Value, M) across positive and negative numbers without #NUM! sign-mismatch errors when guarded for M <> 0.
Operational rules often require asymmetric rounding instead: carton ordering uses CEILING(Value, PackSize) so stock never falls short, while rebate tiers use FLOOR(Value, StepSize) so thresholds are earned only after crossing the full multiple.
| Raw Input | Multiple (M) | Nearest: ROUND(x/M,0)*M | Up: CEILING(x,M) | Down: FLOOR(x,M) |
|---|---|---|---|---|
| 37 units | 5 | 35 | 40 | 35 |
| 38 units | 5 | 40 | 40 | 35 |
| 37.5 midpoint | 5 | 40 | 40 | 35 |
| 14.38 price | 0.25 | 14.50 | 14.50 | 14.25 |
Nearest Multiple Formula (Universal MROUND Equivalent)
Rounds B2 to the closest multiple of C2 (5, 0.25, 15-minute fraction, etc.), rounding exact half-multiples away from zero.
=ROUND(B2 / C2, 0) * C2Upward Pack-Size & Downward Tier-Multiple Formulas
Use CEILING(B2, C2) to round up to full shipping packs and FLOOR(B2, C2) to round down to completed multiples.
=CEILING(B2, C2)Try the example
Use the original values or change them to test your understanding.