The first thing most people do when they type =AVERAGE(A1:A10) is assume Excel tossed out every empty-looking cell. That’s dangerously wrong. AVERAGE ignores only cells that are *truly blank* — not cells with a space, not cells with '0', not cells with a formula returning "", and definitely not cells formatted as text containing numbers. If your report shows $42,850 instead of $37,200, this is why.
The Setup
You’re auditing Q1 sales figures for six regional reps at TechNova Solutions. The raw data lives in A1:B9. Column A is rep names; column B is their reported quarterly revenue. But the source is messy: some reps entered nothing, some typed 'N/A', others pasted numbers as text, and one accidentally added a leading space.
| Rep Name | Q1 Revenue |
|---|---|
| Sarah Chen | $62,400 |
| Diego Morales | $51,100 |
| Priya Patel | |
| Marcus Bell | 0 |
| Lena Wu | "$44,900" |
| Tariq Hassan | $38,200 |
| Anya Petrova | N/A |
| Javier Ruiz | $49,600 |
The Challenge
You need the true average of *actual reported revenues*. Not the average of everything in B2:B9 — that includes text, zeros, and invisible spaces. You also can’t just delete rows: Priya’s blank cell is valid (she hasn’t submitted yet), but Lena’s ""$44,900"" is broken data, not missing data. And Marcus’s zero? That’s a real $0 sale — should it count?
This isn’t about typing =AVERAGE(B2:B9). It’s about knowing what Excel sees versus what you see. Your eyes say “blank” — Excel says “text”, “space”, or “zero”. That mismatch breaks forecasts, bonuses, and trust.
Walking Through It
Step 1: Test what AVERAGE actually calculates right now.
Enter =AVERAGE(B2:B9) in cell D2. Result: $33,133.33. That’s wrong — it included Marcus’s $0 and Tariq’s space-padded value, but ignored Priya’s true blank and Lena’s text.
Step 2: Reveal hidden content.
Select B2:B9. Press Alt + H + F + D (Home → Find & Select → Go To Special → Blanks). Only Priya’s cell (B4) is selected. That proves only one cell is *truly blank*. Now press Ctrl + ` to show formulas — you’ll see Lena’s cell contains "$44,900", not a number.
| Cell | What You See | What Excel Sees | Counted by AVERAGE? |
|---|---|---|---|
| B2 | $62,400 | 62400 (number) | ✓ |
| B3 | $51,100 | 51100 (number) | ✓ |
| B4 | (empty) | ✗ | |
| B5 | 0 | 0 (number) | ✓ |
| B6 | "$44,900" | "$44,900" (text) | ✗ |
| B7 | $38,200 | " $38,200" (text w/ leading space) | ✗ |
| B8 | N/A | "N/A" (text) | ✗ |
| B9 | $49,600 | 49600 (number) | ✓ |
Step 3: Fix the data, not the formula.
• Select B6. Press F2, then Delete — retype as 44900 (no $, no quotes).
• Select B7. Press F2, then Backspace once to remove the leading space.
• Leave B5 (Marcus’s $0) — it’s intentional.
• Leave B4 blank — it’s legitimately missing.
• Replace B8 (“N/A”) with a true blank: double-click, press Delete.
Step 4: Verify with AVERAGEA if needed.
Type =AVERAGEA(B2:B9) in D3. Result: $30,150.00. Why lower? Because AVERAGEA counts text as 0 — so “N/A” became 0, dragging the average down. That’s why AVERAGEA is almost never the right choice for numeric averages.
The Result
After cleaning, your final data range B2:B9 looks like this — and =AVERAGE(B2:B9) now returns $49,240.00. That matches reality: five valid entries ($62,400, $51,100, $0, $44,900, $49,600, $38,200), one true blank ignored.
| Rep Name | Q1 Revenue |
|---|---|
| Sarah Chen | $62,400 |
| Diego Morales | $51,100 |
| Priya Patel | |
| Marcus Bell | 0 |
| Lena Wu | 44900 |
| Tariq Hassan | 38200 |
| Anya Petrova | |
| Javier Ruiz | $49,600 |
What Could Go Wrong
Mistake #1: Assuming "" (empty string) is blank.
If a formula like =IF(C2="","",D2) outputs "" in B6, AVERAGE ignores it — but it’s not blank. It’s text. Excel treats "" as text, not a blank cell. That cell won’t be selected by Go To Special → Blanks.
Mistake #2: Using AVERAGE on a range that includes merged cells.
Merged cells break AVERAGE logic. If B4:B5 is merged and contains $51,100, AVERAGE(B2:B9) reads only B4 — and treats B5 as blank. Always unmerge before averaging.
Mistake #3: Forgetting that filtered data changes everything.
When you filter the table and run AVERAGE, it still calculates over the full range B2:B9 — including hidden rows. Use SUBTOTAL(101,B2:B9) instead. 101 = AVERAGE ignoring hidden rows.
Quick Reference: When AVERAGE Includes or Excludes Cells
| Cell Content | Treated as Blank? | Included in AVERAGE? |
|---|---|---|
| (completely empty) | ✓ | ✗ |
| 0 | ✗ | ✓ |
| "" (formula result) | ✗ | ✗ |
| " " (space) | ✗ | ✗ |
| "N/A" | ✗ | ✗ |
| $44,900 (as text) | ✗ | ✗ |
| #N/A error | ✗ | ✗ |
