Blank, zero and not applicable are three different answers

A cell that looks empty can still make a strong claim.

“0 open safety incidents” says the measure applied and the count was zero. “Not applicable” says the measure did not apply to this record. A blank says only that no usable value is present in that field, unless the dataset defines more.

Those distinctions affect decisions. They change who appears on an eligibility list, which records enter an exception queue, how a rate is calculated, and whether a trend reflects business activity or incomplete collection. Treating all three as interchangeable is not a cleanup shortcut. It changes the meaning of the data.

Consider a monthly vendor-spend table. A reported value of $0 can mean the vendor was in scope and no spend was recorded that month. “Not applicable” might mean the vendor is paid through a type of arrangement for which monthly spend is not the relevant measure. A blank might mean Accounts Payable has not yet matched or supplied the amount.

Replacing blanks with zero makes unresolved work look like no spending. Counting not-applicable records alongside zero-spend vendors can distort the population being compared. Neither error is obvious once the spreadsheet has been summarized.

The denominator is often where the mistake appears

Many metrics are ratios, even when people describe them casually. “Average incidents per active site,” “share of products with a specification,” and “average spend per eligible vendor” all require a defined population.

For average monthly incidents per active site, an active site with zero incidents commonly belongs in the calculation. It is evidence that the site was active and had no incidents. A site for which the incident measure does not apply commonly sits outside that particular denominator. A site with a blank incident count needs a stated rule before it can be included, excluded, or estimated.

That does not mean zero always belongs in a denominator or that not applicable always means “discard this record.” A location without a relevant measure may still matter greatly in another analysis. The point is narrower: each metric needs a rule that identifies its population and explains what each status means within it.

The same issue arises in product data. “0 watts” is a specification for a product that consumes no power. “Not applicable” may be correct for a non-electrical item. A blank wattage may mean the supplier has not provided the specification. Treating the last two states as zero can produce false comparisons and faulty compliance filters.

A blank is not a business definition

People often say a blank means “unknown.” Sometimes it does. Often it does not.

A blank can be a spreadsheet display choice, an empty field in an export, an omitted source value, a delayed submission, a failed validation, a value withheld from a report, or an accidental omission. A database NULL is also not automatically the same thing as a blank cell or an explicit unknown code.

This is more than terminology. SQL-style systems commonly use three-valued logic: true, false, and unknown. In PostgreSQL, for example, NULL represents an unknown result, so comparisons and filters involving it do not behave as though it were zero or false. A condition such as amount <> 0 does not automatically return rows where the amount is unknown. Those rows require explicit handling.

Consequential reporting systems make similar distinctions. The SEC’s EDGAR XBRL guidance says a nil fact should be used only when it conveys a meaning different from both zero and not reporting the fact at all. That is a structured-reporting convention, not a reason to put XBRL rules into every business spreadsheet. It is useful evidence that an empty-looking presentation does not settle what the underlying data means.

Preserve the meaning before transforming the value

For many operational datasets, a lightweight design is enough. Keep the reported value, then add a controlled field that says whether the measure applies. Where the decision stakes justify more detail, add a reason for the missing or unresolved value.

For example:

A lower-stakes dataset may need only one controlled status field, provided its values are defined. The goal is not to create a catalog of codes for every possible situation. It is to avoid forcing several different business states into an empty cell.

A few validation checks can keep the design honest:

• Flag a numeric value paired with “not applicable.”
• Flag an applicable, required field that is blank.
• Review a sudden rise in blanks after a form, vendor, or process change.
• Make summary calculations state how they treat zero, not applicable, and unresolved values.

The original submitted value should remain available whenever practical. Analysts may later need a transformed field for a specific calculation, but the transformation should not erase whether the source reported zero, supplied nothing, or said the question did not apply.

Before filling an empty-looking cell, pause long enough to ask what correction is actually being made. Are you resolving a missing value, inventing a zero, or changing the population? Those are three different actions, and the dataset should let its users tell them apart.