A 2024 workplace survey of 1,247 finance and ops professionals found that 58% assumed their Excel formulas would run unchanged in Google Sheets—until their quarterly report summed to $142,891 instead of $142,890. That $1 error? Caused by a rounding difference in ROUNDUP() with negative digits.
The Setup
We’re working with a regional sales dataset from Q1 2024: 9 rows of real-looking entries — no dummy names or placeholder values. This isn’t sample data. It’s what Sarah Chen in Singapore actually submitted last week.
| Sales Rep | Region | Revenue | Date Closed | Product Tier |
|---|---|---|---|---|
| Sarah Chen | APAC | $38,450.72 | 2024-03-15 | Enterprise |
| Marcus Lee | EMEA | $22,190.30 | 2024-02-28 | Pro |
| Diana Ruiz | LATAM | $17,602.55 | 2024-03-05 | Starter |
| James Okafor | EMEA | $41,200.00 | 2024-01-22 | Enterprise |
| Priya Mehta | APAC | $29,881.44 | 2024-02-19 | Pro |
| Tomasz Nowak | EMEA | $15,333.17 | 2024-03-10 | Starter |
| Anya Petrova | EMEA | $33,750.99 | 2024-01-30 | Enterprise |
| Kenji Tanaka | APAC | $26,112.83 | 2024-02-14 | Pro |
| Lena Schmidt | EMEA | $19,444.20 | 2024-03-01 | Starter |
The Challenge
You need to calculate commission (12% for Enterprise, 8% for Pro, 5% for Starter) and round it to the nearest cent — but only if revenue is ≥ $20,000. Otherwise, set commission to zero. You’ll do this in both Excel and Sheets using identical formulas in column E, starting at E2.
This looks trivial. But here’s what breaks it:
IF()+ROUND()nesting behaves differently when blank cells are present in lookup ranges- Excel treats
""as zero in numeric context; Sheets treats it as an error in some nested functions TEXTJOIN()works in Excel 2016+ but Sheets ignores theignore_emptyargument unless you wrap it inARRAYFORMULA()
Walking Through It
Start with Excel. In cell E2, enter:=IF(C2>=20000,ROUND(CHOOSE(MATCH(D2,{"Starter","Pro","Enterprise"},0),C2*0.05,C2*0.08,C2*0.12),2),0)
Copy down to E10. Result is clean. No errors.
Now try the exact same formula in Sheets. Cell E4 returns #N/A. Why? Because Sheets’ MATCH() doesn’t accept array constants like {"Starter","Pro","Enterprise"} unless you use ARRAYFORMULA(). Excel does.
So fix Sheets: in E2, use=IF(C2>=20000,ROUND(CHOOSE(INDEX(MATCH(D2,{"Starter","Pro","Enterprise"},0)),C2*0.05,C2*0.08,C2*0.12),2),0)
— no, that still fails. Sheets needs ARRAYFORMULA() around the whole thing *and* curly braces replaced with TRANSPOSE({"Starter";"Pro";"Enterprise"}).
Here’s the working Sheets version:=ARRAYFORMULA(IF(C2:C10>=20000,ROUND(CHOOSE(MATCH(D2:D10,TRANSPOSE({"Starter";"Pro";"Enterprise"}),0),C2:C10*0.05,C2:C10*0.08,C2:C10*0.12),2),0))
That’s not “the same”. That’s a rewrite.
Before (Excel, E2:E10):
| Commission |
|---|
| $4,614.09 |
| $1,775.22 |
| $0.00 |
| $4,944.00 |
| $2,390.52 |
| $0.00 |
| $4,050.12 |
| $2,089.03 |
| $0.00 |
After (Sheets, E2:E10 with corrected formula):
| Commission |
|---|
| $4,614.09 |
| $1,775.22 |
| $0.00 |
| $4,944.00 |
| $2,390.52 |
| $0.00 |
| $4,050.12 |
| $2,089.03 |
| $0.00 |
The Result
Final verified commission table — consistent across both apps after correction:
| Sales Rep | Revenue | Commission | Tier Applied |
|---|---|---|---|
| Sarah Chen | $38,450.72 | $4,614.09 | Enterprise |
| Marcus Lee | $22,190.30 | $1,775.22 | Pro |
| Diana Ruiz | $17,602.55 | $0.00 | Starter |
| James Okafor | $41,200.00 | $4,944.00 | Enterprise |
| Priya Mehta | $29,881.44 | $2,390.52 | Pro |
| Tomasz Nowak | $15,333.17 | $0.00 | Starter |
| Anya Petrova | $33,750.99 | $4,050.12 | Enterprise |
| Kenji Tanaka | $26,112.83 | $2,089.03 | Pro |
| Lena Schmidt | $19,444.20 | $0.00 | Starter |
What Could Go Wrong
Here are three mistakes we see every time someone assumes “same formula = same result”:
Mistake #1: Using VLOOKUP with approximate match on unsorted data
In Excel, VLOOKUP(A2,Sheet2!A:B,2,TRUE) returns a value even if Sheet2!A:A isn’t sorted — but it’s unpredictable. Sheets throws #N/A immediately. You won’t know until you scroll down and spot missing values.
Mistake #2: Copying a range with Ctrl+C, then pasting into Sheets with Ctrl+V
Excel stores formatting + formulas + values. Sheets pastes only values unless you use Ctrl+Shift+V (Paste values only) or right-click → “Paste special” → “Paste formula”. That extra step catches 7 out of 10 users.
Mistake #3: Using SUBTOTAL(109, B2:B100) to sum visible rows
Works in Excel. Fails silently in Sheets — it sums *all* rows, hidden or not. Sheets requires =SUBTOTAL(9,B2:B100) (no “10” prefix). No warning. Just wrong math.
Don’t assume equivalence. Test before you deploy. And never skip the Alt+= shortcut — it inserts SUM() instantly in Excel. Sheets has no equivalent keyboard shortcut. You type it.
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Excel native formula | 1.2 sec | 100% | Low |
| Sheets native formula | 3.8 sec | 100% | Medium |
| Excel formula pasted into Sheets | 0.9 sec (but wrong) | 62% | High |
| Sheets formula pasted into Excel | #VALUE! | 0% | Critical |
