Yes, you use =ROUND(number, num_digits) to round numbers in Excel. But if you apply it to intermediate calculations before summing, your final totals will drift — and nobody spots it until audit season.
The Problem
You’re building a Q1 sales summary for six regional reps. Each has a commission rate applied to gross revenue. You calculate commission as =B2*C2, then copy down. The raw results? Messy decimals — some with 14 digits after the decimal point because Excel stores binary approximations of decimal fractions.
Here’s what your worksheet actually looks like in columns A–D (A1:D7):
| Rep Name | Revenue ($) | Rate (%) | Commission (uncalculated) |
|---|---|---|---|
| Sarah Chen | $124,890.00 | 7.5% | $9,366.750000000002 |
| Diego Morales | $87,215.50 | 6.25% | $5,450.968750000001 |
| Amina Patel | $142,600.75 | 8.0% | $11,408.060000000002 |
| Kenji Tanaka | $95,333.20 | 5.75% | $5,481.659000000001 |
| Lena Dubois | $112,450.99 | 6.5% | $7,309.314350000002 |
| Marcus Wright | $138,722.40 | 7.0% | $9,710.568000000002 |
Look at row 2: $9,366.750000000002. That’s not a typo. Excel stores 0.75 as a binary approximation — 0.101100110011… repeating. When you sum D2:D7, you get $48,727.32010000001. Your finance team expects $48,727.32. That extra 0.00010000001 cents triggers a red flag in ERP reconciliation.
This isn’t rounding error. It’s *precision leakage*. And it happens every time you skip explicit rounding on calculated fields.
The Solution
Do this — no exceptions:
- In cell D2, replace
=B2*C2with=ROUND(B2*C2,2). - Press Ctrl+C, select D3:D7, press Ctrl+V.
- In cell D8, enter
=SUM(D2:D7)— not=ROUND(SUM(B2:B7*C2:C7),2). That second version is wrong. We’ll explain why later.
Now your table looks clean and auditable:
| Rep Name | Revenue ($) | Rate (%) | Commission (rounded) |
|---|---|---|---|
| Sarah Chen | $124,890.00 | 7.5% | $9,366.75 |
| Diego Morales | $87,215.50 | 6.25% | $5,450.97 |
| Amina Patel | $142,600.75 | 8.0% | $11,408.06 |
| Kenji Tanaka | $95,333.20 | 5.75% | $5,481.66 |
| Lena Dubois | $112,450.99 | 6.5% | $7,309.31 |
| Marcus Wright | $138,722.40 | 7.0% | $9,710.57 |
| Total | $48,727.32 |
D8 now returns exactly $48,727.32. No floating-point noise. No audit trail confusion.
Here’s the counterintuitive tip: Never wrap SUM() in ROUND(). If you do =ROUND(SUM(B2:B7*C2:C7),2), Excel first computes the imprecise sum (with those hidden 14-digit tails), then rounds the garbage. You’re rounding noise — not values. Do rounding at the source, not the aggregate.
Going Further
Three variations you’ll need within 48 hours of fixing the core issue:
- Round down to nearest $5: Use
=FLOOR.MATH(A1,5). For $123.87 → $120.00. Works on positive numbers only. For negatives, add third argument:=FLOOR.MATH(-123.87,5,1)forces arithmetic rounding toward zero. - Rounding for display only (no calculation impact): Format cells with Custom format
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_). This masks decimals visually but leaves underlying values untouched — dangerous for downstream formulas. Only use in final presentation sheets, never in model logic. - Banker’s rounding (round half to even): Excel’s default ROUND() uses symmetric arithmetic rounding. To match accounting standards, use
=EVEN(A1/0.02)*0.02for rounding to nearest cent — e.g., 2.5 → 2, 3.5 → 4. Yes, it’s ugly. Yes, finance teams demand it.
If your sheet must support multiple currencies, avoid ROUND() entirely for conversion calculations. Instead, use =MROUND(A1*exchange_rate,0.01) — it respects currency-specific rounding rules better than ROUND() when exchange rates have >4 decimals.
Pro move: Press Alt + H + H to open the Format Cells dialog, then go to Number → Currency → set Decimal places to 2. This doesn’t change values — but combined with ROUND() in formulas, it creates visual + logical consistency.
When NOT to Use This
Stop rounding if any of these apply:
- You’re calculating interest accruals where regulatory rules require truncation, not rounding (e.g., SEC Rule 10b-10). Use
=TRUNC(B2*C2,2)instead — it chops digits without adjusting. - Your data comes from an external API that delivers exact decimal strings (like JSON with "123.45000000000002"). ROUND() won’t fix string-to-number parsing errors. Clean at ingestion:
=VALUE(SUBSTITUTE(A1,".",""))/100for two-decimal strings. - You’re working with scientific measurements where significant figures matter more than decimal places. ROUND() destroys sig figs. Use
=ROUND(A1,LOG10(A1)-2)to round to 3 significant digits — e.g., 12345 → 12300, 0.004567 → 0.00457.
Biggest trap: rounding percentages before using them in weighted averages. If you round 6.789% to 6.79%, then multiply by $1M, you lose $10 vs. the unrounded version. Always keep % values at full precision until final display.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Format Cells dialog | Ctrl + 1 | Fastest way to adjust decimal display |
| Insert function dialog | Shift + F3 | Type “ROUND” to auto-select |
| Toggle formula view | Ctrl + ` (backtick) | See all ROUND() formulas at once |
| Apply Accounting format | Ctrl + Shift + $ | Sets 2 decimals + $ sign + right-align |
| Edit formula in cell | F2 | Critical for checking nested ROUND() logic |
