ROUND, ROUNDUP, and ROUNDDOWN: Rounding, Rounding Up, and Rounding Down (Decimal Places, Negative Digits, Tax, and Currency)
A report’s “number quality” depends on its rounding rules. Define the rule and lock it in with a function to ensure consistency.
Syntax and Core Concepts
| Function | Syntax | Description |
|---|---|---|
| ROUND | =ROUND(number, num_digits) | Rounds to the nearest value (0: whole number, 2: second decimal place, -2: hundreds) |
| ROUNDUP | =ROUNDUP(number, num_digits) | Rounds away from zero |
| ROUNDDOWN | =ROUNDDOWN(number, num_digits) | Rounds toward zero |
| MROUND | =MROUND(number, multiple) | Rounds to the nearest multiple |
| CEILING.MATH | =CEILING.MATH(number, [significance], [mode]) | Rounds toward +∞ (to a multiple) |
| FLOOR.MATH | =FLOOR.MATH(number, [significance], [mode]) | Rounds toward −∞ (to a multiple) |
Excel ROUND rule: .5 is rounded away from zero (for example, ROUND(-2.5,0)=-3).
ROUND — Standard Rounding
Round to two decimal places
=ROUND(B2, 2)Round to a whole number
=ROUND(B2, 0)Round to the nearest hundred (negative digit)
=ROUND(B2, -2)ROUNDUP/ROUNDDOWN — Always Round Up or Down
Round shipping costs up to the nearest 100
=ROUNDUP(B2, -2)Truncate a unit price to two decimal places
=ROUNDDOWN(B2, 2)Round up to a whole number after calculating VAT
=ROUNDUP(Subtotal*0.1, 0)Rounding to Multiples — MROUND/CEILING.MATH/FLOOR.MATH
Round to the nearest 50
=MROUND(B2, 50)Always round up to the nearest 100
=CEILING.MATH(B2, 100)Always round down to 0.25-hour increments
=FLOOR.MATH(B2, 0.25)Negative-number note: CEILING.MATH(-3.2,1)=-3, FLOOR.MATH(-3.2,1)=-4 (the direction can appear different depending on the sign).
10 Practical Patterns
① Cost × margin → retail price (round to the nearest 10)
=MROUND(Cost*(1+Margin), 10)② Round the post-discount total to a whole number
=ROUND(SUM(Subtotal)-Discount, 0)③ Round time records up to 5-minute increments
=CEILING.MATH(Minutes/1440, 5/1440)④ Round inventory order quantities up to packs of 12
=CEILING.MATH(Demand, 12)⑤ Show KPIs to one decimal place (preserve calculations)
=ROUND(Measure, 1)⑥ Currency rounding and international decimal places
=ROUND(Amount*Rate, 2)⑦ Report monthly sales in millions
=ROUND(Sales/1000000, 1)⑧ Round change down to the nearest 1,000
=FLOOR.MATH(Cash-Price, 1000)⑨ Round grades to 0.5 increments
=MROUND(Score, 0.5)⑩ Compare with TRUNC and INT
=TRUNC(-2.3) = -2 // Truncates the decimal portion
=INT(-2.3) = -3 // Toward -∞Pitfalls and Checklist
- Display formatting vs. calculation → Changing only the cell format leaves the underlying value unchanged. Add ROUND for calculations.
- Cumulative errors → Decide whether to round before or after totaling, and document the policy.
- Sign and direction confusion → Test CEILING and FLOOR directions with negative numbers.
- Multiple standard → Define and reference company standards such as 0, 5, 10, 50, and 100 (manage them with defined names).
Summary
| Goal | Typical Formula |
|---|---|
| Standard rounding | ROUND(x, n) |
| Always round up/down | ROUNDUP/ROUNDDOWN(x, n) |
| Round to a multiple | MROUND(x, m) |
| Round up/down to a multiple | CEILING.MATH / FLOOR.MATH |
| Negative digits (tens/hundreds) | n=-1,-2,... |