Yes, no, blank, unknown: four states that are often collapsed into two

Consider a supplier-review field: Certificate on file? Four records appear nearly identical at a glance:

Only “yes” and “no” answer the question. The other two describe the state of the information.

That distinction can look fussy in a spreadsheet. It stops being fussy when the column feeds a filter, count, queue, forecast, or automated decision.

A filter for suppliers with no certificate should normally return recorded no values. If it also returns blanks and unknowns, it mixes confirmed deficiencies with records that may not have been reviewed at all. The resulting list may be larger, but it is less truthful and less useful.

An answer is different from the evidence behind it

“Yes” and “no” describe a business fact as recorded: a contract exists or does not; insurance is verified or is not; a company operates in a jurisdiction or does not.

“Unknown” says something different. The question is applicable, an answer is expected, and the answer is not known at the relevant time. That is useful information. It can trigger research, a request for documentation, or a temporary hold.

A blank says less unless the field definition gives it a meaning. It might mean the question was never asked. It might mean an import omitted the field, a source did not support it, a reviewer has not started, or someone made an error. It may even be valid for a nonrequired field.

None of those meanings is automatically “no.”

Database logic provides a helpful, limited analogy. SQL recognizes true, false, and unknown in Boolean logic, rather than treating an unknown value as false. Mature data standards often use still more detail, with separate reasons such as not asked, declined, not applicable, unsupported, and error.

Most business datasets do not need that full vocabulary. They do need to avoid turning materially different states into the same label by accident.

The counting problem is often hidden

Suppose 100 customer records contain a has_current_contract field:

• 55 are confirmed yes
• 20 are confirmed no
• 15 are unknown
• 10 are blank

What is the contract rate?

There is no single correct number until the denominator is stated. Among records with a confirmed answer, the rate is 55 out of 75, or 73.3%. Across all records, it is 55%. If blanks and unknowns are silently counted as no, the reported rate is also 55%, but the interpretation has changed: uncertainty has been recast as a confirmed absence.

A useful report can show the rate among confirmed answers and the excluded count beside it. For example: “Current contract confirmed for 55 of 75 answered records; 25 records were unknown or unreviewed.”

That wording tells readers how much confidence to place in the percentage and where further work may matter.

A small design change prevents larger problems

For a consequential yes/no field, separate the answer from its status or reason. A compact design might look like this:

• has_current_insurance: yes / no / null
• insurance_status: verified / unknown / not_asked / declined / not_applicable / source_error
• checked_on: date
• source_reference: source or document identifier

This is not an argument for building a taxonomy around every cell. A low-stakes internal note may only need a required yes/no choice. But when an absent answer changes routing, eligibility, outreach, compliance review, or reporting, the reason for absence is part of the record.

The same approach also makes data cleanup safer. A team can later decide how to handle not_applicable without confusing it with a recorded no. It can send unknown items to a research queue, while treating source_error as a data-quality issue. Those are different actions because they describe different conditions.

A practical audit for an existing column

Before relying on an important yes/no field, inspect its actual contents, not just its header. Ask:

1. What distinct values occur, including empty cells, spaces, “N/A,” and text such as “pending”? 2. Which values are recorded answers, and which are workflow states? 3. Does the data-entry process permit blanks? If so, what causes them? 4. What does each state cause a person or system to do? 5. Does the current filter, dashboard, or formula preserve those distinctions?

One minimum rule is especially durable: do not convert blanks to no unless a written field definition explicitly authorizes it. If the source does not define blank values, count and report them separately.

A field can remain simple while still being honest. The payoff is not theoretical neatness. The next person can tell whether to proceed, investigate, request information, or disregard a question that does not apply. That reduces avoidable rework and keeps uncertainty from quietly becoming a decision.