Rounding and remainders
7 minutes · 4 steps · free, no sign-up
Sheets often show fewer decimal places than they hold, which can make totals look wrong. Rounding fixes the number itself, and there are different rules for different jobs: round to the nearest penny, always round up to full boxes, or find the remainder after dividing.
- Round to a set number of decimal places
- Always round up with ROUNDUP, for things like boxes
- Find what is left over with MOD
- Round to a multiple, such as the next 50p, with CEILING
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=ROUND(x, 2) nearest, 2 decimal places
=ROUNDUP(x, 0) always up
=ROUNDDOWN(x, 0) always down (towards zero)
=MOD(47, 12) remainder: 11
=CEILING(x, 0.5) up to the next multiple of 0.5
=FLOOR(x, 0.5) down to a multiple
=INT(x) down to a whole numberThe key ideasSummary: Rounding and remainders
Formatting a cell to show two decimals only changes how it looks. ROUND changes the value, so later formulas use the rounded number. Use it when the exact figure matters, such as money you will really pay.
Use ROUNDUP when you cannot buy part of something: 47 items in boxes of 12 is 3.92 boxes, so you need 4. MOD gives the remainder (11 spare items in the last box), and CEILING and FLOOR round to multiples such as the next 50p.
Watch out forCommon mistakes
- Relying on formatting instead of ROUND. A total that adds visible pennies may differ by a penny from the sum of unrounded values.
- Using ROUND where ROUNDUP is needed. ROUND(3.2, 0) gives 3 boxes when you need 4.
- Surprises with negatives. ROUNDDOWN and INT treat negative numbers differently. INT(-2.5) is -3, while ROUNDDOWN(-2.5, 0) is -2.
- Rounding too early. Round at the last step of a calculation, not in the middle, or the small errors add up.
Next stepTry it in the real Google Sheets
These work the same in Excel. Google Sheets also has MROUND, which rounds to the nearest multiple, either up or down.