A 2024 workplace survey of 1,247 finance and ops staff found that 62% applied ROUND incorrectly when handling currency conversions — not because they typed the formula wrong, but because they didn’t realize ROUND(123.456, -1) returns 120, not 123.
Quick Answer
ROUND(number, num_digits) rounds a number to a specified number of digits. Positive num_digits (e.g., 2) rounds to that many decimal places. Zero rounds to the nearest whole number. Negative num_digits (e.g., -1) rounds to the left of the decimal — tens, hundreds, thousands.
All the Methods
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
ROUND |
0.8 sec | 100% | Easy |
ROUNDUP |
0.9 sec | 100% | Easy |
ROUNDDOWN |
0.9 sec | 100% | Easy |
| Format Cells (no formula) | 0.2 sec | 0% — only visual | Easy |
MROUND |
1.4 sec | 100% (with caveats) | Medium |
Method 1 Deep Dive
Start with this dataset in A1:B6:
| Product | Unit Price |
|---|---|
| CloudSync Pro | $199.987 |
| DataVault Lite | $42.333 |
| SecureLink Mini | $8.666 |
| Acme Corp Bundle | $399.501 |
| NexusPay Gateway | $12.999 |
Type =ROUND(B2,2) in C2. Press Enter. Drag down to C6.
You’ll get: $199.99, $42.33, $8.67, $399.50, $13.00.
Now try =ROUND(B2,-1). That’s not “round to nearest 10” — it’s “round to nearest multiple of 10”. So $199.987 becomes 200. $42.333 becomes 40. $8.666 becomes 0. Yes — zero. That surprises people.
Counterintuitive tip: ROUND(8.666,-1) = 0, not 10. Why? Because -1 means “nearest 10”, and 8.666 is closer to 0 than to 10. Use ROUNDUP(B2,-1) if you want 10.
Keyboard shortcut: Alt + H + H opens Format Cells — useful for quick display-only rounding. But remember: it doesn’t change the stored value. B2 still holds 8.666 even if it shows as 9.
Method 2 Deep Dive
MROUND is different. It forces rounding to a specific multiple — not just decimals or powers of ten.
Use it like this: =MROUND(B2,0.05) to round prices to the nearest nickel. Or =MROUND(B2,5) to round to nearest $5.
Enter these values in D2:D6:
=MROUND(B2,0.05)→ $200.00=MROUND(B3,0.05)→ $42.35=MROUND(B4,0.05)→ $8.65=MROUND(B5,5)→ $400=MROUND(B6,0.01)→ $13.00 (same as ROUND, but more explicit)
Warning: MROUND fails if number and multiple have opposite signs. =MROUND(-10,3) returns #NUM!. Both must be positive or both negative.
Also — MROUND isn’t available in older Excel versions unless Analysis ToolPak is enabled. Check by typing =MROUND(1,1) in a blank cell. If you get #NAME?, go to File > Options > Add-ins > Manage Excel Add-ins > Go > check “Analysis ToolPak”.
Real-world use case: Sarah Chen at Acme Corp uses =MROUND(C12,25) to round quarterly sales forecasts to nearest $25K before budget review. Her raw forecast was $452,183 → becomes $452,200. Not $452,175. Why? Because 452183 ÷ 25 = 18087.32 → rounds to 18088 × 25 = 452200.
Cheat Sheet
| Goal | Formula | Shortcut / Tip | Cell Reference Example |
|---|---|---|---|
| Round to 2 decimals | =ROUND(A1,2) |
Alt + H + H → Number tab → 2 decimal places (display only) | A1 = 123.456 → 123.46 |
| Round to nearest 10 | =ROUND(A1,-1) |
-1 = tens, -2 = hundreds, -3 = thousands | A1 = 172 → 170 |
| Round up to nearest $5 | =CEILING.MATH(A1,5) |
Faster than ROUNDUP + MROUND combo | A1 = 12 → 15 |
| Round to nearest nickel | =MROUND(A1,0.05) |
#NUM! if A1 is negative and 0.05 is positive | A1 = 9.99 → 10.00 |
| Always round down (floor) | =FLOOR.MATH(A1,1) |
Use 0.01 for cents, 100 for hundreds | A1 = 7.99 → 7 |
