Why does your AVERAGEIF return #VALUE! when the data looks clean? Why does STDEV.S give wildly different results than your colleague’s identical formula? Why does filtering by date break your correlation chart?
All three happen because Excel doesn’t care about your intent—it only responds to structure. And most people skip the structural prep step before running stats.
The Setup
You’re handed raw survey responses from a Q3 vendor satisfaction audit across 9 regional offices. No headers were standardized. Some dates are text (‘2024-05-12’), some are true dates (45422). One column mixes ‘N/A’, ‘NULL’, and blank cells. There are duplicate entries for ‘TechNova Ltd’ — same ID, different scores.
| Vendor ID | Vendor Name | Region | Score | Survey Date | Status |
|---|---|---|---|---|---|
| V782 | TechNova Ltd | APAC | 4.2 | 2024-05-12 | Active |
| V782 | TechNova Ltd | APAC | 3.8 | 2024-05-12 | Active |
| V319 | BlueSky Logistics | EMEA | 4.6 | 2024-04-28 | Active |
| V405 | Acme Corp | NA | 3.1 | 45422 | Inactive |
| V661 | Vertex Solutions | APAC | 2.9 | 2024-05-03 | Active |
| V227 | Orion Group | EMEA | 4.4 | 45420 | Active |
| V555 | StellarWorks Inc | NA | N/A | 2024-04-30 | Pending |
| V782 | TechNova Ltd | APAC | 4.0 | 2024-05-12 | Active |
| V102 | Zephyr Dynamics | APAC | 3.7 | 45419 | Active |
The Challenge
You need to report: average score per region, standard deviation of scores in EMEA, count of vendors with score > 4.0 in APAC, and correlation between Survey Date and Score. But you can’t run any of that until three things are fixed:
- Text dates must convert to serial numbers (so DATEVALUE or paste-special works)
- Duplicates must be flagged—not deleted—because one may have a corrected status
- N/A and blanks must become numeric gaps (so STDEV.S ignores them, but COUNTA doesn’t)
If you try AVERAGEIF before cleaning, Excel treats ‘N/A’ as text and returns 0 — not an error, just wrong.
Walking Through It
Start at A1. Your raw data lives in A1:F10. Do this in order — no skipping.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select F2:F10 → Home → Find & Select → Replace → Find ‘N/A’ → Leave ‘Replace with’ blank → Replace All | All ‘N/A’ become blanks. Still not numeric — but now they’ll be ignored by STDEV.S | Ctrl+H |
| 2 | Select E2:E10 → Data → Text to Columns → Delimited → Next → Next → Column data format: Date (YMD) → Finish | All dates become true serial numbers. ‘2024-05-12’ = 45442; ‘45422’ stays 45422. | Alt+A+E |
| 3 | In G2, enter: =IF(COUNTIFS($A$2:$A$10,A2,$E$2:$E$10,E2)>1,"DUPE","OK") → Drag down to G10 | G2: “DUPE”, G3: “OK”, G8: “DUPE”. Now you see which rows need review — not deletion. | Enter → Ctrl+D |
| 4 | Select D2:D10 → Home → Conditional Formatting → Highlight Cells Rules → Greater Than → 4.0 → Light Red Fill | Cells D2, D3, D6, D8 now highlighted. Visual confirmation before counting. | Alt+H+L+G |
Now your data is statistically safe. No more #VALUE! from mismatched types.
The Result
This is what your cleaned, ready-to-analyze table looks like — with duplicates flagged and dates normalized. You now run these formulas without fear:
- =AVERAGEIFS(D2:D10,C2:C10,"APAC") → 3.97
- =STDEV.S(IF(C2:C10="EMEA",D2:D10)) → 0.14 (use Ctrl+Shift+Enter if not O365)
- =COUNTIFS(C2:C10,"APAC",D2:D10,">4") → 2
- =CORREL(E2:E10,D2:D10) → -0.23 (slight negative trend)
| Vendor ID | Vendor Name | Region | Score | Survey Date | Status | Flag |
|---|---|---|---|---|---|---|
| V782 | TechNova Ltd | APAC | 4.2 | 45442 | Active | DUPE |
| V782 | TechNova Ltd | APAC | 3.8 | 45442 | Active | DUPE |
| V319 | BlueSky Logistics | EMEA | 4.6 | 45417 | Active | OK |
| V405 | Acme Corp | NA | 3.1 | 45422 | Inactive | OK |
| V661 | Vertex Solutions | APAC | 2.9 | 45432 | Active | OK |
| V227 | Orion Group | EMEA | 4.4 | 45420 | Active | OK |
| V555 | StellarWorks Inc | NA | #N/A | 45419 | Pending | OK |
| V782 | TechNova Ltd | APAC | 4.0 | 45442 | Active | DUPE |
| V102 | Zephyr Dynamics | APAC | 3.7 | 45419 | Active | OK |
What Could Go Wrong
These aren’t hypotheticals. They’re the top 3 errors I’ve seen derail real reports:
- Using AVERAGE instead of AVERAGEIF on filtered data: If you filter Region = APAC, then run =AVERAGE(D2:D10), Excel still averages all 9 rows — not just visible ones. Use SUBTOTAL(101,D2:D10) instead.
- Forgetting array behavior in older Excel versions: =STDEV.S(IF(C2:C10="EMEA",D2:D10)) returns #VALUE! unless you press Ctrl+Shift+Enter. In Excel 365, it auto-spills. In Excel 2019? You’ll get zero — silently.
- Running CORREL on mixed date formats: If column E contains both 45422 and ‘2024-05-12’, CORREL treats text as 0 — skewing the result downward. Always validate with =ISNUMBER(E2) before correlating.
Do this next: Open your last vendor file. Run =ISNUMBER(E2) on five random date cells. If any return FALSE, go back to Step 2. Don’t assume — verify.
