The first thing most people do when asked 'How do I know my Excel skill level?' is open a blank workbook and type =SUM(A1:A10). That’s like judging your swimming ability by dipping a toe in the pool. You’re not testing navigation, error handling, or workflow logic — just muscle memory for one function.
The Setup
Let’s use a real sales operations dataset from Q1 2024 — not fake 'Sample Data' but actual field names, inconsistent formatting, and realistic noise. This is what Sarah Chen (Sales Ops Lead at Acme Corp) handed me last Tuesday. She said: 'It looks fine in the pivot table — but the numbers don’t match finance’s report.'
| Rep Name | Region | Q1 Revenue | Close Date | Status |
|---|---|---|---|---|
| Liam Torres | EMEA | $24,850 | 2024-01-12 | Closed |
| Maya Patel | APAC | $31,200 | 2024-02-03 | Closed |
| Diego Ruiz | Americas | $19,750 | 2024-01-28 | Pending |
| Aisha Johnson | EMEA | $28,400 | 2024-03-15 | Closed |
| Kenji Tanaka | APAC | $35,900 | 2024-02-22 | Closed |
| Zara Williams | Americas | $17,600 | 2024-01-09 | Lost |
| Omar Hassan | EMEA | $22,300 | 2024-02-18 | Pending |
| Nina Kim | APAC | $41,500 | 2024-03-05 | Closed |
| Tariq Ali | Americas | $26,100 | 2024-02-29 | Closed |
| Elena Petrova | EMEA | $18,900 | 2024-01-17 | Lost |
The Challenge
Sarah needs three things done — and each reveals a different layer of skill:
- Calculate average revenue per closed deal, excluding Pending/Lost deals — but only if Close Date falls in Q1 (Jan–Mar 2024). A simple
AVERAGEIFwon’t cut it because you need two conditions on different columns. - Flag reps whose Q1 revenue is above regional average — meaning you must compute averages by Region first, then compare each row against its own region’s average. This requires dynamic lookup, not static references.
- Add a column showing days since close date, but return
N/Afor non-Closed deals — no #VALUE! errors, no blanks that look like zeros.
What makes this tricky isn’t the formulas themselves — it’s knowing which tool fits which job. Should you use FILTER? SUMPRODUCT? XLOOKUP? Or go full Power Query? The wrong choice adds 10 minutes and breaks when new rows arrive.
Walking Through It
We’ll tackle each task step-by-step — and rate each solution on clarity, scalability, and error resilience. Start with your raw data in A1:E11.
Task 1: Avg Revenue for Closed Q1 Deals
✅ Correct approach: =AVERAGEIFS(C2:C11,E2:E11,"Closed",D2:D11,">=2024-01-01",D2:D11,"<=2024-03-31") in F2.
❌ Common mistake: Nesting IF inside AVERAGE, which forces array entry (Ctrl+Shift+Enter) in older Excel — and fails silently if entered wrong.
The beauty of AVERAGEIFS is it handles multiple criteria natively — no Ctrl+Shift+Enter needed. And it ignores text or blanks automatically.
Task 2: Above Regional Average?
✅ Correct: In G2, enter =C2>XLOOKUP(B2,$B$2:$B$11,$F$2:$F$11,"N/A",0) — but wait. That won’t work. Why? Because $F$2:$F$11 holds region averages — but we haven’t calculated them yet.
So first, in F2:F4, list unique regions (EMEA, APAC, Americas), then in G2: =AVERAGEIFS($C$2:$C$11,$B$2:$B$11,F2,$E$2:$E$11,"Closed"). Now back to H2: =C2>XLOOKUP(B2,$F$2:$F$4,$G$2:$G$4).
💡 Surprising tip: Use F2:F4 as a spill range instead of typing regions manually — select F2, type =UNIQUE(FILTER(B2:B11,E2:E11="Closed")), hit Enter. Excel auto-fills. No copy-paste. No manual updates.
Task 3: Days Since Close (for Closed only)
✅ Correct: In I2, =IF(E2="Closed",TODAY()-D2,"N/A"). But what if D2 is empty? Then TODAY()-"" = 45,322 — nonsense.
Better: =IF(OR(E2<>"Closed",D2=""),"N/A",TODAY()-D2). Even better: =LET(d,D2,s,E2,IF(OR(s<>"Closed",d=""),"N/A",TODAY()-d)) — clean, readable, reusable.
The Result
Here’s what your final table looks like after all steps — with real outputs computed as of 2024-04-10:
| Rep Name | Region | Q1 Revenue | Close Date | Status | Avg Closed Rev | Above Reg Avg? | Days Since |
|---|---|---|---|---|---|---|---|
| Liam Torres | EMEA | $24,850 | 2024-01-12 | Closed | $24,883 | FALSE | 90 |
| Maya Patel | APAC | $31,200 | 2024-02-03 | Closed | $38,700 | FALSE | 67 |
| Diego Ruiz | Americas | $19,750 | 2024-01-28 | Pending | $26,100 | FALSE | N/A |
| Aisha Johnson | EMEA | $28,400 | 2024-03-15 | Closed | $24,883 | TRUE | 26 |
| Kenji Tanaka | APAC | $35,900 | 2024-02-22 | Closed | $38,700 | FALSE | 48 |
| Zara Williams | Americas | $17,600 | 2024-01-09 | Lost | $26,100 | FALSE | N/A |
| Omar Hassan | EMEA | $22,300 | 2024-02-18 | Pending | $24,883 | FALSE | N/A |
| Nina Kim | APAC | $41,500 | 2024-03-05 | Closed | $38,700 | TRUE | 36 |
| Tariq Ali | Americas | $26,100 | 2024-02-29 | Closed | $26,100 | FALSE | 39 |
| Elena Petrova | EMEA | $18,900 | 2024-01-17 | Lost | $24,883 | FALSE | N/A |
What Could Go Wrong
Three mistakes I see daily — each with real consequences:
- Using
CTRL+Twithout checking headers: If your Region column has a blank row or merged cells above row 1, Excel creates a Table that omits data or shifts ranges. Always pressAlt+N+T, then verify the preview highlights exactly A1:E11 before confirming. - Hard-coding regional averages: Typing
=C2>24883instead of linking to a dynamic cell. When EMEA’s average changes next quarter, 12 reports break — and no one knows why. - Ignoring implicit date conversion: Writing
=D2-"2024-01-01"works — but only because Excel auto-converts the string. Write=D2-DATE(2024,1,1)instead. Otherwise,"2024-13-01"returns 0 — not an error — and corrupts calculations silently.
Here’s how to self-score your skill level using this exercise:
| Skill Tier | You Can… | Time to Complete | Error Rate |
|---|---|---|---|
| Foundational | Use AVERAGEIF, basic IF, and format dates | 12–18 min | ≥2 manual fixes |
| Proficient | Build AVERAGEIFS + XLOOKUP combo, spot implicit conversions | 6–9 min | 0–1 fix |
| Advanced | Use LET/SEQUENCE/FILTER to eliminate helper columns entirely | ≤4 min | None — all formulas validate on first try |
