Blank Cells, Zeros and Empty Strings: The Average Branch Sold 44,375. Or 35,500. Or 29,583.

The March consolidation is twelve branches and 355,000.00 of sales, and it went into the board pack with one line under it: average branch, 35,500.00. The regional director's own sheet, built from the same twelve rows, says 44,375.00. Finance's version says 29,583.33.

Nobody typed a number over a formula. All three are AVERAGE or something very close to it, all three are defensible, and the gap between the highest and the lowest is 14,791.67. The branch bonus triggers at 40,000.00, which sits between the first two figures, so the choice of formula is also the choice of whether the network beat target or missed it.

The disagreement is not about arithmetic. It is about two cells that are empty, two cells that contain a real zero, and two cells that contain something which looks empty and is not. Excel has a different opinion about those cells depending on which function you ask, and none of the differences are visible on screen.

What this covers. SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, ISBLANK, LEN, IF, IFERROR, COUNTIF, AVERAGEIF and SUMPRODUCT work in every version this century, and everything essential here is built from them. XLOOKUP, LET and AVERAGEIFS need a newer build; where one appears, the older equivalent sits beside it.

1) Two Columns, One Total, Two Different Counts

Column C is what head office keyed in from twelve branch emails. Column D is the same month pulled out of the till system by a formula. Put them side by side and they are the same column twice:

=SUM(C2:C13) and =SUM(D2:D13) both return 355,000.00. =AVERAGE(C2:C13) and =AVERAGE(D2:D13) both return 35,500.00. Every displayed character is identical.

Now ask how many entries each column has:

C12 and C13 are genuinely empty — Norwich and Swansea never sent an email. D12 and D13 are not empty at all. The till-system pull is =IFERROR(VLOOKUP(A12,Till!A:B,2,FALSE),""), there was no record to find, and so those two cells contain a zero-length string: a real value, of type text, with no characters in it. It occupies the cell. It just has nothing to draw.

🎯 Scenario: Before you trust any total on an inherited sheet, put =COUNT(), =COUNTA() and =SUMPRODUCT(--ISBLANK()) under each numeric column. Three numbers, ten seconds, and they tell you which of the four kinds of nothing you are dealing with before a single average gets quoted at anybody.

2) Three Averages, and the Bonus That Turns On One of Them

The three figures from the opening are these, and all three are the correct answer to a different question:

AVERAGE skips genuinely empty cells and skips text, but it does not skip zeros. So 355,000.00 is divided by ten: the eight branches that traded plus Aberdeen and Plymouth, which reported a real zero. SUM/ROWS divides by twelve, which quietly treats "never reported" as "sold nothing". AVERAGEIF(...,">0") divides by eight and drops both real zeros along with both blanks.

There is a fourth, and it is the one that most often reaches a board pack, because regional tables are built first and averaged afterwards:

Average those six usable regional averages and you get 36,079.17 — a fifth number, from the same 355,000.00, because averaging averages weights a one-branch region the same as a two-branch one.

So: 44,375.00, 36,079.17, 35,500.00, 29,583.33. The bonus threshold is 40,000.00. One of those four clears it.

🎯 Scenario: Write the denominator down in words before you pick a function — "per branch that reported" or "per branch we own". Then put =COUNT() next to the average so the denominator is visible on the page. An average with its own count beside it cannot be quietly re-based by the next person to open the file.

3) The Four Kinds of Nothing

Everything in this article comes out of one small table. A cell that displays nothing is one of four things:

Read the first row again. A genuinely empty cell is equal to "" and equal to 0 at the same time. No actual value can do that: ""=0 is FALSE. An empty cell is not a value at all, so Excel coerces it to whatever the comparison needs, and it obliges in both directions.

That single row is why ISBLANK and =C2="" are not interchangeable, and why the choice between them decides whether your check catches two cells or four.

4) COUNT, COUNTA, COUNTBLANK: Where Each One Draws the Line

• COUNT counts numbers. Text, blanks, logicals and errors are all skipped. On column D it returns 10 — the eight trading branches plus two real zeros — which is exactly the denominator AVERAGE used.
• COUNTA counts cells that are not empty. A zero-length string is not empty, so COUNTA counts it. On column D it returns 12.
• COUNTBLANK counts empty cells and formulas that return "". This is documented behaviour, not a quirk. On column D it returns 2.
• ISBLANK is the strict one. It is TRUE only for a cell with nothing in it. =SUMPRODUCT(--ISBLANK(D2:D13)) returns 0.
• LEN returns 0 for both an empty cell and a zero-length string, and 1 for a cell holding a single space — which makes =LEN(D2)=0 a good test for "displays nothing" and a bad test for "is empty".

Which leaves the arithmetic that gives this section its point:

=COUNTA(D2:D13)      →  12
=COUNTBLANK(D2:D13)  →   2
                        ──
                        14  ... across a range of 12 cells

Both functions are behaving as designed. D12 and D13 are simultaneously "not empty" and "blank", so each is counted once by each function. On column C, where the gaps are real, the same two formulas give 10 and 2 and add to 12.

That sum is the fastest test there is. =COUNTA(range)+COUNTBLANK(range)-ROWS(range)*COLUMNS(range) returns the number of zero-length strings hiding in a range, and it returns 0 when there are none.

🎯 Scenario: Put that one formula at the bottom of every column you import. It is a single cell, it needs no helper column, and a non-zero result tells you the range contains cells that will pass some of your checks and fail others.

5) Where Empty Strings Come From

Nobody types a zero-length string. They arrive, and always from one of these:

1. =IF(condition,"",value) — the single most common source. Written to keep a report tidy, it manufactures a value whose entire job is to look like the absence of one.
2. =IFERROR(lookup,"") or =IFNA(lookup,"") — this is where column D's came from. The lookup failed, the wrapper suppressed the #N/A, and what it put in its place was not nothing.
3. Power Query. A null loaded to a sheet lands as a genuinely empty cell, but a column transformed with Replace Values to "", or built from a text step that returns an empty result, lands as a zero-length string.
4. CSV and system exports. Two adjacent delimiters — ,, — usually import as empty, but an exported quoted empty field — ,"", — imports as a zero-length string. The same report from the same system can do both in different columns.
5. Copy-paste from the web or a database client, where "no value" was rendered as an empty cell in HTML.
6. Paste Special → Values over a column of IF(...,""), which converts the formulas to constants and keeps the zero-length strings as constants. This one is worth knowing: flattening a sheet does not clean it.

The thread running through all six is that a zero-length string is what you get when something tried to be tidy. It always looks better than #N/A and it is always worse, because #N/A propagates loudly and "" propagates silently.

6) A Lookup Returns 0 for an Empty Cell

The regional report does not read the consolidation sheet directly. It looks each branch up:

=XLOOKUP(A2,Consol!$A$2:$A$13,Consol!$C$2:$C$13,"branch not found")
=VLOOKUP(A2,Consol!$A$2:$C$13,3,FALSE)
=INDEX(Consol!$C$2:$C$13,MATCH(A2,Consol!$A$2:$A$13,0))

All three return 0 for Norwich Riverside. Not blank, not an error, not "branch not found" — the number zero, formatted with the rest of the column, sitting in a report next to Aberdeen Union's identical and entirely real zero.

Two things are going on, and both are worth stating plainly:

A reference to an empty cell returns 0. =C12 on an empty C12 gives 0.00. Lookups are references, so they inherit this. It applies to VLOOKUP, XLOOKUP, INDEX, OFFSET and a plain =Sheet2!A1 alike.

XLOOKUP's fourth argument does not help. if_not_found fires when the lookup value is not in the lookup array. Norwich is in the array — row 12, right where it should be. The lookup succeeded perfectly and returned the contents of an empty cell. The argument is doing its job; its job is simply not this one.

The fix is to test the result rather than the lookup, which is what LET is for:

=LET(v,XLOOKUP(A2,Branch,Sales,"not found"),IF(v="","not reported",v))

Without LET, the same thing costs you the lookup twice:

=IF(XLOOKUP(A2,Branch,Sales)="","not reported",XLOOKUP(A2,Branch,Sales))

Note that v="" is TRUE for a genuinely empty source cell and for a zero-length string, which here is exactly what you want: both mean "no figure", however they got that way. And do not reach for =XLOOKUP(...)&"" to force a blank-looking result — it turns every real figure into text, and the column stops summing.

🎯 Scenario: Any lookup that lands in a column people will total needs to distinguish three outcomes, not two: found a number, found nothing, and did not find the row. Two of those must never render as 0.00, because 0.00 is a claim about the business.

7) AVERAGEIFS by Region: 18,950, 29,750 and #DIV/0!

One formula, filled down seven regions:

=AVERAGEIFS($C$2:$C$13,$B$2:$B$13,H2)
=AVERAGEIF($B$2:$B$13,H2,$C$2:$C$13)      (older builds)

Three regions show what the three kinds of nothing do to a conditional average:

Scotland → 18,950.00. Leith sold 37,900 and Aberdeen Union reported a real 0. AVERAGEIFS counts the zero, so the divisor is 2 and Scotland's average is half of Leith's figure. Correct, and it will still be read as "Scotland is collapsing".

Wales → 29,750.00. Cardiff Bay sold 29,750 and Swansea Marina is empty. AVERAGEIFS skips the empty cell entirely, divisor 1, and Wales's average is exactly Cardiff's number. The missing branch has left no trace at all.

East → #DIV/0!. Norwich Riverside is the only branch in the region and it is empty. No numeric cells match, so there is nothing to divide by. This is the one case that announces itself — and the reflex is to wrap it in =IFERROR(...,0), which converts the only honest cell on the table into a fake zero that will be averaged into the national figure next month.

The same three behaviours, once more in a row: a zero counts, a blank does not, and nothing at all errors. If Swansea's cell had held "" instead of being empty, Wales would still read 29,750.00 — text is skipped exactly like a blank. And if Aberdeen's zero had been left empty because "zero and blank are the same thing", Scotland would read 37,900.00 and the estate would look 18,950.00 healthier than it is.

🎯 Scenario: Put =COUNT() beside every conditional average, with the same criteria. Scotland showing 18,950.00 over a count of 2 and Wales showing 29,750.00 over a count of 1 tells the whole story in one glance; either number alone tells a false one.

8) Arithmetic: One Coerces, One Explodes

Take the variance column, =C2-40000, filled down all twelve rows:

Four branches, all showing exactly −40,000.00, and only two of them earned it. Column E on the grid above is that column: the blanks have already turned into a number, and from this point on nothing downstream can tell which is which. =SUM(E2:E13) is −125,000.00; the honest figure, against the ten branches that actually reported, is −45,000.00.

An empty string does the opposite:

=D12-40000     →  #VALUE!
=D12*1.2       →  #VALUE!
=SUM(D2:D13)   →  355,000.00   (SUM skips text, so this one is fine)

A blank is coerced to 0 and flows on silently. A zero-length string is text, and text minus a number is an error. Neither behaviour is wrong; what is wrong is expecting one and getting the other.

Which is why the useful comparison is not "which is safer" but which one fails where you will see it. The empty string at least stops. The blank produces a plausible number and puts it in a variance report.

The fix is the same in both directions — decide, in the formula, what a missing figure means before the arithmetic touches it:

=IF(C2="","",C2-40000)             leave it visibly absent
=IF(C2="",NA(),C2-40000)           make every downstream formula say so too
=IF(ISBLANK(C2),NA(),C2-40000)     only the genuinely unreported

The third is the precise one. The first two treat a real zero-length string the same as a real gap, which is usually right and occasionally not.

9) Charts, PivotTables, Sorting and Ctrl+End

Away from formulas, the two behave differently again — and this is where a sheet that passed every check still misleads somebody.

Charts. Select Data → Hidden and Empty Cells offers Gaps, Zero and Connect data points with line. That setting applies to genuinely empty cells only. A cell holding "" is not empty, so it is plotted as zero whatever the setting says — a line that dives to the axis for the two months nobody reported. The reliable way to get a gap is =NA(), which every chart type renders as a break, at the cost of #N/A appearing in the cell.

PivotTables. Both show as (blank) in the row area, which is the source of the false confidence. But Count counts a zero-length string as an item and skips an empty cell, so a pivot's Count and its Count of Numbers can disagree by exactly the number of "" cells underneath.

Sorting. Genuinely empty cells always sort to the bottom, ascending or descending — they are not sorted at all, they are pushed to the end. A zero-length string is text and sorts as text: to the top of a descending sort, above the numbers. Sort a column with both in it and the two kinds of nothing end up at opposite ends of the sheet.

Go To Special → Blanks. Selects genuinely empty cells only. This is the tool most people use to find and fill the gaps, and it is blind to every zero-length string in the range — so a sheet can be "checked for blanks" and still be full of them.

Ctrl+End and file size. The used range extends to the last cell containing anything, and "" counts. A column of =IF(...,"") dragged to row 50,000 gives a workbook 50,000 rows of used range, a scrollbar that reaches the horizon, and a file several megabytes larger than its contents.

10) Deciding What Nothing Means, Then Writing It Down

None of this is fixable by choosing better functions, because the ambiguity is upstream of the spreadsheet. Before the formula there is a question with a real answer: did the branch sell nothing, or do we not know what it sold? Those are different facts about the world, and a sheet that stores them in the same way has thrown information away that no formula can recover.

So encode them differently, and encode them on purpose:

That last row deserves the argument spelled out. The usual reason for =IF(A2=0,"",A2) is that a column of zeros looks bad. But Options → Advanced → Show a zero in cells that have zero value, or the custom number format #,##0.00;-#,##0.00;"", hides zeros without changing what is in the cell. You get the clean-looking report and the values stay numbers. Formatting is the right tool for how something looks; a formula that returns "" changes what it is.

🎯 Scenario: On any sheet other people fill in, add a status column — Reported / Zero / Awaited — and drive the checks off that instead of off the shape of the sales cell. It takes one column of data validation and it makes every downstream formula unambiguous, because you are no longer inferring intent from emptiness.

11) Cleaning Up Zero-Length Strings

You have inherited the sheet and column D is full of them. COUNTA plus COUNTBLANK came to 14. Here is the route that works, in order of preference:

1. Fix the source. If the column is still formulas, change =IFERROR(x,"") to =IFERROR(x,NA()) and the problem is gone at the root. Everything downstream now either shows a figure or refuses to pretend.

2. Power Query. If the data is loaded through Get & Transform, add a Replace Values step replacing "" with null — you will need Advanced Options to enter null — and every one of them lands on the sheet as a genuinely empty cell. This is the only route that stays fixed when the data refreshes.

3. On a flattened column, the reliable sequence is:

• In a helper column, =IF(D2="",NA(),D2), filled down.
• Copy the helper, Paste Special → Values over itself. The #N/As are now constants, not formula results.
• Select the range, Go To Special → Constants, untick everything except Errors, and press Delete.
• The zero-length strings are now genuinely empty cells, and every real value is untouched.

That fourth step is why the helper column goes through NA() rather than something friendlier: Go To Special has no "select the zero-length strings" option, but it does have "select the errors", so the trick is to turn one into the other for exactly as long as it takes to delete them.

What does not work, and is worth knowing so you do not lose twenty minutes to it: Find & Replace cannot search for a zero-length string, Go To Special → Blanks does not select them, and Paste Special → Values preserves them perfectly.

12) Twelve Traps

1. COUNTA for "how many did we get". It counts every "" from every suppressed lookup. Use COUNT for numbers, or SUMPRODUCT(--(range<>"")) for anything.
2. ISBLANK on an imported column. Almost always FALSE, almost always misleadingly so. =C2="" is the check you meant.
3. =IFERROR(anything,0). Converts every failure into a measurement. IFERROR(...,"") is barely better; IFNA is narrower and safer, because it lets #DIV/0! and #VALUE! through instead of swallowing them.
4. A lookup landing in a totals column. An empty source cell returns 0.00, and 0.00 sums.
5. AVERAGE after a Remove Duplicates or a filter-and-delete. The denominator moved and the formula did not tell you.
6. Wrapping a #DIV/0! from AVERAGEIF in IFERROR(...,0). The error meant "no data in this group", and you replaced it with "this group averaged zero".
7. Conditional formatting on =$C2=0. Highlights the genuinely empty cells too, because an empty cell equals 0.
8. Data validation "ignore blank". It ignores genuinely empty cells; a "" is a value and gets validated.
9. =A1&B1 to build a key. Blanks concatenate to nothing, so "North" + blank and blank + "North" produce the same key.
10. Charts with =IF(...,"") in the series. Plotted as zero, whatever Hidden and Empty Cells is set to. Use NA().
11. A trailing space instead of an empty cell. Passes ISBLANK as FALSE, fails =C2="", is counted by COUNTA and not by COUNTBLANK, and TRIM will not remove it from a cell that contains only spaces — it returns "", which is where you started.
12. Ctrl+End landing in row 50,000. Formulas dragged past the data, returning "". Delete the surplus rows entirely — clearing the contents is not enough — and save.

Practice

Use the grid above: branch in A, region in B, the keyed figure in C, the till-system pull in D, the variance in E, rows 2 to 13.

1. Establish the difference. Write =COUNT, =COUNTA, =COUNTBLANK and =SUMPRODUCT(--ISBLANK(...)) for both C and D. Then write the single formula that returns the number of zero-length strings in a range, and confirm it gives 0 for C and 2 for D.
2. Four averages. Produce 44,375.00, 36,079.17, 35,500.00 and 29,583.33, each from one formula, and label each with the denominator it used. State which one you would put next to a 40,000.00 bonus threshold, and why.
3. The equality that cannot be. In three empty cells write =C12="", =C12=0 and =C12=D12, then the same three against D12. Explain the two results that differ.
4. The lookup. Build a small report that looks up Norwich Riverside and Aberdeen Union. Get it to show 0.00 for one and something honest for the other, without turning the column to text.
5. The regional average. Build the seven-region AVERAGEIFS with a matching COUNT beside it. Then set Swansea Marina's cell to 0 and record which regions' numbers move, and by how much.
6. Clean it. Convert D12 and D13 to genuinely empty cells using the NA() and Go To Special → Constants → Errors route, and confirm afterwards that COUNTA plus COUNTBLANK is back to 12.

Summary

A cell that displays nothing is one of four things, and Excel disagrees with itself about them on purpose. A genuinely empty cell equals both 0 and "". A zero-length string equals "" but not 0, is counted by COUNTA and by COUNTBLANK, and is invisible to ISBLANK and to Go To Special → Blanks. A real zero is a measurement and belongs in the denominator. A space is none of the above and looks like all of them.

None of that is obscure, and none of it matters until an average has to be quoted. Then it decides the number: 44,375.00 or 35,500.00 or 29,583.33, from twelve rows totalling 355,000.00 that every formula agrees about.

So the practical version is short. Put =COUNT() beside every average, so the denominator is on the page and not in somebody's head. Run =COUNTA(range)+COUNTBLANK(range)-ROWS(range) on anything imported, because a non-zero result means half your checks are lying. Never write "" when you mean "no value" — leave the cell alone and fix the appearance with a number format. And store "sold nothing" and "we don't know" differently, because they are different facts, and the sheet is the last place they can still be told apart.

The alternative is this month: two branches that never sent an email, four cells reading −40,000.00, and a bonus that pays or does not depending on which correct formula somebody happened to write.