ROUND & friends — Excel guide
Controlling precision on purpose: standard rounding, always-up and always-down, and snapping to multiples. The difference between a formatted display and a genuinely rounded value matters everywhere money foots.
ROUND
Round to a number of digits — .5 always goes AWAY from zero.
- ROUND changes the stored VALUE; number formatting only changes the display. Downstream math notices the difference.
- Penny-off invoice totals: ROUND each line to 2 decimals so stored = printed.
- No banker's rounding here — 2.5→3 and -2.5→-3, always.
ROUNDUP
Always round away from zero — partial units count as whole ones.
- ROUNDDOWN truncates toward zero; TRUNC is its twin. INT differs on negatives: INT(-2.5) = -3, ROUNDDOWN(-2.5, 0) = -2.
- Boxes needed, hours billed, licenses required — anything you can't buy a fraction of is a ROUNDUP.
MROUND
Round to the nearest MULTIPLE of anything.
- CEILING rounds UP to a multiple, FLOOR rounds DOWN — CEILING(23, 5) = 25, FLOOR(23, 5) = 20.
- Nickel rounding: MROUND(A2, 0.05). The .99 trick: CEILING(A2, 1) - 0.01.
- EVEN and ODD round away from zero to the next even/odd integer.