Chapter 05 · Making it computable · 7 min
Blank is not zero
Zero is a measurement. It says we looked, and the answer was none.
A blank cell is not a measurement. It says nothing was recorded here — and that covers the driver who was off shift, the field the form did not ask for, the row the export truncated, and the value somebody deleted last Tuesday. All of it arrives looking the same.
Treating the second as the first is the most expensive small mistake in a spreadsheet, because it does not produce an error. It produces an average.
The grid
Six drivers and their trips on one Monday, in A1:B7. Column C is somebody's tidied-up copy of column B, built with a formula — so before you run anything, the grid shows you that formula as text rather than its result. Run any example below and column C computes.
Two drivers with nothing, for two different reasons
Ben's cell holds 0. He was on shift and completed no trips. That is a real observation about a real Monday.
Chong's cell is empty. Nobody has said whether he worked. The row exists because he is on the roster, not because anything was measured.
Start where the difference does not show up. SUM treats a blank as nothing to add and a zero as nothing to add, so the total is the same either way — and that is exactly why the problem stays hidden. The first number anyone looks at is the one number that cannot see it.
Four, zero, blank, six, zero, five. What does the total come to?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | driver | trips | trips_tidied | ||
| 2 | Aisyah | 4 | =IF(B2=0,"",B2) | =SUM(B2:B7) | |
| 3 | Ben | 0 | =IF(B3=0,"",B3) | ||
| 4 | Chong | =IF(B4=0,"",B4) | |||
| 5 | Devi | 6 | =IF(B5=0,"",B5) | ||
| 6 | Elena | 0 | =IF(B6=0,"",B6) | ||
| 7 | Farid | 5 | =IF(B7=0,"",B7) |
Where it does show up
An average has a denominator, and the denominator is a decision.
Excel's own answer is to skip blanks entirely: the total is divided by how many numbers it found, not by how many rows you can see. Six drivers on screen, five numbers among them.
So the average below is fifteen over five. If Chong's blank had meant zero instead, it would be fifteen over six — 2.5. Same fifteen trips, same six drivers, two different headline numbers, and nothing on the screen tells you which one the person who built the sheet intended.
Fifteen trips are spread over six drivers, but one of the six has an empty cell. Does the answer come out at 2.5, or at something higher?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | driver | trips | trips_tidied | ||
| 2 | Aisyah | 4 | =IF(B2=0,"",B2) | ||
| 3 | Ben | 0 | =IF(B3=0,"",B3) | =AVERAGEIFS(B2:B7,B2:B7,">=0") | |
| 4 | Chong | =IF(B4=0,"",B4) | |||
| 5 | Devi | 6 | =IF(B5=0,"",B5) | ||
| 6 | Elena | 0 | =IF(B6=0,"",B6) | ||
| 7 | Farid | 5 | =IF(B7=0,"",B7) |
Measure the hole before you fill it
COUNTBLANK counts the empty cells in a range. It is the only one of the counting functions whose whole job is the absence, and it answers the question you should ask before deciding anything: how much of this column is actually missing?
One blank out of six is a judgement call. Four blanks out of six means the column is not really a column yet, and no amount of filling in will change that — you go back to whoever produced the file.
Run it, then notice that it costs one formula and you now know something you were previously guessing about.
Look down the trips column and count the cells with nothing in them at all. Remember that a zero is not one of them.
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | driver | trips | trips_tidied | ||
| 2 | Aisyah | 4 | =IF(B2=0,"",B2) | ||
| 3 | Ben | 0 | =IF(B3=0,"",B3) | ||
| 4 | Chong | =IF(B4=0,"",B4) | =COUNTBLANK(B2:B7) | ||
| 5 | Devi | 6 | =IF(B5=0,"",B5) | ||
| 6 | Elena | 0 | =IF(B6=0,"",B6) | ||
| 7 | Farid | 5 | =IF(B7=0,"",B7) |
Asking one cell directly
ISBLANK answers for a single cell: is there genuinely nothing here?
It is stricter than it looks, and that strictness is the useful part. It is TRUE only for a cell that has never held anything — not for a cell holding a zero, not for a cell holding a space, and not, as the next section shows, for a cell that merely looks empty.
B4 is Chong's row, and it is the real thing.
Nothing has ever been typed into that cell. Does the answer come back TRUE or FALSE?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | driver | trips | trips_tidied | ||
| 2 | Aisyah | 4 | =IF(B2=0,"",B2) | ||
| 3 | Ben | 0 | =IF(B3=0,"",B3) | ||
| 4 | Chong | =IF(B4=0,"",B4) | |||
| 5 | Devi | 6 | =IF(B5=0,"",B5) | =ISBLANK(B4) | |
| 6 | Elena | 0 | =IF(B6=0,"",B6) | ||
| 7 | Farid | 5 | =IF(B7=0,"",B7) |
The empty cell that is not empty
Now column C, the tidied copy. Whoever made it wanted the zeros to stop cluttering the report, so every cell holds a formula that returns the trip count — or, when the count is zero, an empty string: two quote marks with nothing between them.
On screen the result is indistinguishable from a blank. C3, C4 and C6 look exactly as empty as B4 does.
They are not. Each one holds a formula, and that formula returned a piece of text that happens to have no characters in it. Ask ISBLANK about C4 and it disagrees with your eyes.
That cell shows nothing at all after the formulas run. Does the strict test agree that there is nothing in it?
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | driver | trips | trips_tidied | ||
| 2 | Aisyah | 4 | =IF(B2=0,"",B2) | ||
| 3 | Ben | 0 | =IF(B3=0,"",B3) | ||
| 4 | Chong | =IF(B4=0,"",B4) | |||
| 5 | Devi | 6 | =IF(B5=0,"",B5) | ||
| 6 | Elena | 0 | =IF(B6=0,"",B6) | =ISBLANK(C4) | |
| 7 | Farid | 5 | =IF(B7=0,"",B7) |
Two functions, one range, impossible arithmetic
This is where an empty string stops being a curiosity and starts producing wrong numbers.
COUNTA counts anything that is not empty, and a formula result counts — even an empty one — so it finds six filled cells in C2:C7. COUNTBLANK is more generous about what it will call blank, and it counts the three empty strings, so it finds three.
Six filled plus three blank, in a range of six cells. Both answers are correct by their own definitions and they cannot both be describing the same thing. Run COUNTA over the tidied column and compare it against the five you would get from column B.
Filled cells in the tidied column
In E7, count the non-empty cells in the tidied column, then change the range to the original trips column and watch the answer drop.
Scroll to see all 5 columns →
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | driver | trips | trips_tidied | ||
| 2 | Aisyah | 4 | =IF(B2=0,"",B2) | ||
| 3 | Ben | 0 | =IF(B3=0,"",B3) | ||
| 4 | Chong | =IF(B4=0,"",B4) | |||
| 5 | Devi | 6 | =IF(B5=0,"",B5) | ||
| 6 | Elena | 0 | =IF(B6=0,"",B6) | ||
| 7 | Farid | 5 | =IF(B7=0,"",B7) |
What to actually do
None of the four states is wrong to have. They are wrong to mix, and the fix is a decision you write down rather than a function you apply.
- A zero is a measurement. Leave it. Deleting zeros to make a report look tidy is how column C happened.
- A blank is a gap. Count them first, then decide per column whether a gap means zero, means unknown, or means the row should not be there.
- An empty string is a formula's opinion about presentation, and it has no business in a column anyone will compute on. If a report needs to hide zeros, hide them with number formatting, not by replacing the value.
- An error value is the honest one. It refuses to be averaged and it makes you look at it.
And write the decision in the sheet, beside the data, in words: blank means the driver was not rostered. That sentence is worth more than the formula that acts on it, because in three weeks it is the only thing that will tell you whether the average was right.
Next in this track: where the raw data goes, where the summary goes, and why the two must never be the same block of cells.