Blank, zero, and the value the form invented

The situation

In January, Sherine Barsoum in sourcing was asked to settle an argument that had run for two years. How long does fabric really take, from the day a purchase order is confirmed to the day the roll is in the store?

She had a field for it. Every order record at Nasseef carries days from PO to fabric in-house. She had 46 orders shipped in the previous year. She exported them, took the average, and reported 26.4 days. The planning rule that came out of it was simple: book fabric 28 days before the cut date. It went into the intake sheet in February and stayed there all year.

It was wrong by a week. The arithmetic that produced it was perfect.

What the export really contained

Of the 46 orders, the field was filled on 35 and empty on 11. The spreadsheet averaged what it could see.

Sherine went back in November. She rebuilt the missing 11 from the mill's delivery notes, which had been in a folder the whole time.

The true answer was 34.1 days. The planning rule was set at 28. In the year that followed, nine orders started cutting with fabric that had not arrived. Between them they lost 41.4 line-days. Nasseef costs an idle line-day at USD 1,180, which is its own fully-absorbed figure. So the rule cost USD 48,852.00.

Filling in those 11 blanks would have taken about twenty minutes each, from the mill's paperwork. Nasseef costs a merchandiser's time at USD 9.60 an hour, so the work was worth USD 35.20. Divide 48,852.00 by 35.20 and you get 1,388. That is the price of the damage against the price of the work nobody did.

The finding, and it applies to every blank you have

Look at the two group means again. The orders with the field filled averaged 26.4 days. The orders with it blank averaged 58.6, which is 2.22 times longer.

That is not bad luck. It is the mechanism, and once you have seen it you will see it everywhere. A field goes blank for reasons, and the reasons are usually about what the value would have said. Nobody goes back to close out a data field on an order that has already gone badly. The merchandiser is on the phone about the delay. The sourcing officer is chasing the mill. The order ships late and somebody who was not there files the paperwork.

So the blanks were not a random 24% of the sample. They were, near enough, the disasters. An average taken over the records that were filled is therefore not an estimate of the truth. It is a survey of the occasions when answering was easy.

That turns the usual instinct around. You look at a column that is 76% full and the 76% reassures you. The right reaction is to ask what the missing quarter has in common. If the answer is "they went wrong", the number on the page is not slightly off. It is flattering you, in a direction you can name.

A blank is not a zero, and both are honest

Now the other half. It is smaller and sharper.

Nasseef's end-line inspection log has a field for the day's rejects. Over 22 working days last quarter it was filled on 14 days. Those days recorded 412 rejects against 24,180 pieces inspected. It was left empty on the other 8 days, which covered 14,720 pieces.

Two reports were run off that log in the same week. One divided the 412 rejects by every piece produced in the 22 days. The other divided by the pieces on days where the reject field had a value.

The threshold sits between the two answers. One report says the factory is comfortably inside its contract. The other says it is outside. Both were computed correctly from the same table. The whole difference is whether eight blank days entered the denominator.

Which is right? Neither, and that is the honest answer. If the eight blank days ran at the same rate as the fourteen measured ones, the true figure is 1.70%. If they really had no rejects, it is 1.06%. The blank did not make the number wrong. It made the number a range. The report printed one end of that range as a fact, with nothing on the page saying it had done so.

A recorded zero is a completely different thing. Zero says somebody looked and found none. Blank says nobody is telling you anything. They are not two shades of one colour. Treating them as one is how a range becomes a fact.

The third kind of empty: the value the form supplied

The worst of the three is not empty at all.

A form that will not save without a value will get one. In practice it gets whatever is quickest: the first item in the list, today's date, or the number that was there last time.

At Nasseef the sample dispatch record would not save without a dispatch date, and it offered the day the record was created. On TH-4180 the record was created on a Monday. The parcel actually went on the Sunday six days later. Thurlestone's comment window is fourteen days from dispatch. So Rowaida spent the whole approval chasing against a clock that had started six days early. That is 42.9% of the float, given away by a default nobody chose.

This is the one to be angriest about. It is the only kind of empty that lies. A blank announces itself. A defaulted value looks exactly like a value somebody knew. Nothing on the screen, in the export or in the report tells them apart afterwards.

What to do on Monday

1. Take one column you plan from and count the blanks. Not the percentage. The actual records. Then look at what those records have in common. If they share an outcome, every average you have taken over that column is biased in a direction you can now name.
2. Write down what a blank means in your records. One line: empty means the value is not yet known; a value that does not apply is written as not applicable; a measured none is written as a zero. Three states, and you can tell them apart in an export.
3. Find the fields your forms will not let you leave empty. For each one, ask what a person does when they do not know. That answer is already in your data.
4. Never average a column with blanks in it without saying how many there were. Not because the average is wrong, but because the reader is entitled to know how much of the population it describes.

I am about to plan from an average taken over a column in my records, and the column has blanks in it. Help me work out whether the average is safe to use.

I will paste or describe the column, how many records there are in total, how many have a value, and what the average of the filled ones is. Ask me for anything you need that I have not given you.

Work through this with me in order. One: what does a blank in this particular column most likely mean — nobody has measured it yet, it does not apply to that record, or somebody measured it and did not write it down. Two: is there any reason to think the blanks are NOT a random sample — in particular, would a record be more likely to be left blank precisely because of what the value would have said. Three: if that is plausible, tell me which direction the average is biased in, and say so as a direction rather than as a number, because you cannot know the size. Four: tell me the cheapest place I could recover the missing values from — another document, another department, somebody's paperwork. Five: write me the one sentence I should put beside this number when I report it, so a reader knows how much of the population it describes.

Do not estimate the missing values for me. I want to know whether the number is safe, not a fabricated version of it.