A missing value means the expected information is not available or not applicable under the stated rules. A zero is an observed or explicitly defined quantity. Do not replace one with the other merely to make a chart or calculation complete.
Ask what the field is supposed to mean
Start with the business definition and the point at which the value is collected. An empty sales amount might represent an unfinished order, a failed import or an amount that does not apply. A recorded zero might mean a free service, no activity during the period or a deliberate system default. Those meanings require different handling.
Look at the source process before writing a cleaning rule. Ask the person responsible for collecting or maintaining the field how a blank arises. A technical inspection can identify empty cells, but it cannot by itself establish the business reason for every absence.
Separate absence, inapplicability and collection delay
Create a small classification for the cases that matter to the decision. You might distinguish “not yet reported”, “not applicable” and “unknown”. Use explicit status fields where that is practical, rather than expecting readers to infer the meaning of punctuation or cell colour.
Preserve the original value and the reason for any transformation. If an upstream export uses an empty string to mean “no activity”, document that convention and test whether it is applied consistently. If the convention is uncertain, keep the uncertainty visible instead of silently asserting a zero.
Inspect the effect on the calculation
Different functions and tools handle blanks differently. Before publishing a metric, explain which records enter its numerator and denominator. Replacing missing observations with zeros can lower an average; excluding them can make the available sample appear more representative than it is.
Neither choice is universally correct. The right approach depends on the field's meaning, the decision and the reason information is missing. A reader should be able to reconstruct the calculation and see how much of the expected population was actually observed.
Investigate the pattern, not just the count
Count missing values by source, period, category or operational step. Concentration can reveal a collection problem that a global percentage hides. Missing records from one region, a new channel or a particular type of customer may also change how a comparison should be interpreted.
A pattern is a starting point for investigation, not proof of a cause. Compare the technical result with source documentation and operational knowledge. When you cannot resolve a material absence, state the limit on the report rather than presenting an adjusted dataset as unquestionable truth.
Make the reporting choice understandable
Use a note close to the affected measure. Explain the expected population, observed population, treatment of absent values and any important exclusions. A chart can distinguish unavailable data from a measured zero through labels and a legend; it should not rely on colour alone.
Assign a source owner to the unresolved collection issue and a report owner to the publication decision. Those are related responsibilities, but they are not identical. A report may need a cautious release before the underlying process is fully repaired, provided its limits are clear and the intended use remains appropriate.
Illustrative worked example
A fictional scenario to explain the method, not a customer result or a market benchmark.
An illustrative weekly service report expects activity values from five branches: 12, 8, 0, blank and 10. The zero belongs to a branch that confirmed no activity. The blank belongs to a branch whose submission has not arrived. The four reported branches total thirty units and average 7.5 units.
If the team turns the blank into zero, the displayed five-branch average becomes six. If it excludes the branch without a note, readers may assume all branches were represented. The team instead reports thirty observed units, four of five branches reporting and one confirmed zero-activity branch. It marks the overall picture as incomplete.
The missing submission has an owner and a follow-up date. When it arrives, the report can be updated with a clear explanation. These fictional values illustrate treatment of absence; they do not establish a universal rule for statistical imputation or a claim about an actual organisation.
TAKE THIS INTO YOUR NEXT CONVERSATION
A practical next-step checklist.
- Define what a blank and a zero mean in this field.
- Keep delayed, unknown and inapplicable values distinguishable.
- Explain which observations enter a total or average.
- Assign an owner to unresolved collection problems.
Common questions.
Should blanks always be excluded from an average?
No. Exclusion can be appropriate for some definitions but misleading for others. First establish why the observation is missing and what population the average is intended to describe.
Can an automated rule decide which missing values should become zero?
It can apply an agreed, validated convention. It cannot establish the underlying business meaning merely from the fact that a cell is empty.
Further reading & source context.
These primary references provide background. They are not evidence for the fictional example above and do not endorse Accuralytix.
Found an error or a missing qualification? Send an editorial correction.
Check my data readiness.
Use a practical tool to turn this idea into a clearer starting point.
Check my data readiness