Separate zero, absence, confidential, estimated and provisional values before calculations and charts.
Updated :
AI-generated illustration
Can an empty cell be treated as zero?
An observation includes a value and its status. Codes vary across producers and files; a letter has no universal meaning. Keep the table’s notes.
Read the exact legend
Find the code list and structure version. Keep value, status, unit and note in separate fields.
Classify absences
Separate not collected, not applicable, unpublished, confidential and extraction errors. Preserve an unknown cause when undocumented.
Define the calculation
State expected population and usable observations. Never silently replace missing with zero; explain exclusions and imputations.
Show coverage
Publish available counts alongside the result. Retain estimated and provisional flags; show a missing chart point differently from zero.
From observation to decision
A mean over 72 values, not 100 rows
A table expects 100 rows: 72 usable numeric values, 18 missing and 10 withheld as confidential. The 72 available values total 720 units.
Fictional learning case: counts and dates illustrate the method and do not describe a real assignment.
Availability of 100 expected values
Scale from 0 to 100 rows; three mutually exclusive categories, total: 100.
Usable72
Missing18
Confidential10
Case reading grid
Observation
Useful check
Next action
72 usable values
720 / 72 = 10 units.
Publish the available-value mean with its count: 72 of 100.
18 missing values
Their values and reasons for absence need examination.
Do not automatically replace them with zero.
10 confidential values
Withholding does not mean zero activity.
Retain the status; do not bypass protection.
Your decision — Why is 720 / 100 = 7.2 not the observed mean?
Explained answer
It would implicitly assign zero to the 28 unavailable values. That adds an assumption absent from the table and can artificially shift the result.
Reference: Eurostat’s legend distinguishes a series break, provisional status and unavailable values. Read the legend of the dataset used; the case figures are fictional. Eurostat — Database: flags and coding conventions
Situations and decisions
Typical situations for preparing a check. They do not describe completed assignments or actual observations.
A numeric code enters the mean
The fictional calculation in the ACS module shows why 10, 20 and a missing-value code are not three observed quantities. The available mean is 15 over two observations; replacing the missing value with zero adds an assumption.
Confidentiality
A suppressed cell does not mean no activity. Do not reconstruct it by subtraction to bypass disclosure protection.
The denominator changes the message
Fictional example: six of ten expected organisations supply an amount; two report zero and four do not respond. Coverage is 6/10, or 60%. The positive share among respondents is 4/6, about 66.7%. Saying 40% of organisations have a positive amount does not establish the situation of the four unknown cases.
A revised series is more than a corrected cell
Illustrative case: an extract shows a provisional 100, then 104 after revision. Keep both versions and their dates. Compare periods using a consistent vintage or disclose partial revisions; do not silently replace the original files.
Worked exercise
Three valid responses out of five expected
Fictional example: the values and documents teach the method and do not describe a real organisation.
Five units are expected. The fictional dictionary distinguishes observations, non-response and masked values.
If the table exceeds the screen, scroll horizontally; arrow keys work when the region is focused.
Starting information
Item
Value
Context
A
10
Observed
B
0
Observed
C
Blank
Non-response
D
20
Observed
E
Confidential
Masked
Your checks
Separate both missing states from observed zero.
Calculate the available-value mean.
State coverage and the target population.
Read the answer
Answer and decision
The available sum is 30 and the mean of three observations is 10. Coverage is 3/5, or 60%. Dividing by five gives 6 by implicitly replacing two unknowns with zero. The available mean does not establish the mean of all five units.
If the case changes
If imputation is needed, disclose its rule and a separate result; leave raw values intact.
Record to retain
Keep missing codes, expected/observed counts, available mean and coverage caveat.
Some rows remain usable for other variables. Document rules by variable and measure lost coverage.
Is provisional data false?
The flag indicates possible revision, not established falsity. Cite the release and check revisions before comparing.
How should a series display a missing observation?
Leave a gap or an explained missing symbol. Connecting points can suggest an unobserved trajectory. Display any useful interpolation separately and state its method instead of presenting it as a published measurement.
TOOLS / REGISTERS
Information to record
Publish available counts alongside the result. Retain estimated and provisional flags; show a missing chart point differently from zero.
Eurostat: separate observation and confidentiality status
Since 27 January 2025 Eurostat has split flags between OBS_STATUS and CONF_STATUS. Dissemination codelists checked on 22 October 2026 confirm these codes. Keep letter and field together: indiscriminate lowercasing can alter meaning.
Eurostat: separate observation and confidentiality status
Reference
Value or form
What it identifies
b
OBS_STATUS
Time-series break. Read metadata before calculating change across it; the code alone establishes neither cause nor size.
d
OBS_STATUS
Different definition: consult metadata. An identical variable label does not establish comparable observations.
p
OBS_STATUS
Provisional value. Retain extraction date and status to distinguish later revisions from economic change.
i
OBS_STATUS
Value imputed by Eurostat or another receiving agency, rather than identified as directly collected.
m
OBS_STATUS
Missing value: data cannot exist. Neither measured zero nor an absence whose cause may be invented.
bdip
OBS_STATUS
Published combination: break, different definition, imputation and provisional value. Retain the full code, not its first letter.
C
CONF_STATUS
Confidential, in CONF_STATUS. Migration documentation replaces the former dissemination c with this code.
N
CONF_STATUS
Not for publication. An unpublished figure does not establish zero activity.
P
CONF_STATUS
Information under non-statistical secrecy arrangements. Uppercase P does not mean provisional, unlike OBS_STATUS p.
Implication for the record
Conventions may differ across formats and production systems. In this service former s and z became i and m; do not generalize the mapping without documentation. Keep value, status, confidentiality and notes separate.
Can an old file containing z use today’s legend?
First identify version and dissemination channel. Preserve the original code and document any mapping rather than silently rewriting an archive as current-format data.
Empty cells: CSV syntax alone does not determine meaning
W3C CSVW separates string value and semantic value. Quoting an empty string is not a data distinction in that model. These are CSVW rules, not universal SQL or spreadsheet import behavior.
Empty cells: CSV syntax alone does not determine meaning
Reference
Value or form
What it identifies
CSVW · a,,z
""
Middle field is an empty string; CSVW defaults to semantic null, not zero.
CSVW · a,"",z
""
Quoted empty and unquoted empty have the same string value in the model.
null · default
[""]
Default null marker is the empty string, not universal NA.
null · example override
["NA"]
Example override ["NA"] replaces the default. Include both markers to recognize both. This is a teaching convention, not actual producer metadata.
datatype
string / integer / date …
Datatype controls conversion. Invalid non-null integer text is an error, not zero.
required
required = true
Required is separate: recognized null fails a required-column constraint even with valid CSV syntax.
Implication for the record
Read column metadata before aggregating. One domain’s missing marker can be another’s valid code. Dropping missing cells changes rate denominators.
Can quoting preserve empty string versus null?
Not by itself in CSVW. Declare markers and types and test the actual reader.
SQL NULL: unknown is not another spelling of false
SQL has three-valued logic. These comparisons and truth-table rules are documented in PostgreSQL 18, independent of any dataset. They explain discarded outer-join rows.
SQL NULL: unknown is not another spelling of false
Reference
Value or form
What it identifies
NULL = NULL
NULL
NULL = NULL is unknown, not proof of equality. column = NULL is not a missingness test.
NULL IS NULL
TRUE
IS NULL tests absence, not the four-character string NULL.
NULL IS NOT DISTINCT FROM NULL
TRUE
IS NOT DISTINCT FROM treats two NULLs as not distinct. Missing IDs still do not prove real-world identity and may create many matches.
FALSE AND NULL
FALSE
FALSE AND unknown is FALSE; this does not guarantee left-to-right evaluation.
TRUE OR NULL
TRUE
TRUE OR unknown is TRUE; replacing every unknown with false does not reproduce complex logic.
NOT NULL
NULL
NOT unknown remains unknown; negation need not recover every discarded row.
WHERE condition
TRUE
WHERE retains only TRUE, discarding FALSE and unknown although they differ.
Implication for the record
Distinguish missingness, empty strings and literal NULL text. Use null-equal comparisons only for an explicit need; repeated absent keys can create artificial links.
Is WHERE NOT condition the complete complement?
Not if condition can be unknown. Unknown rows fail both. Specify treatment, such as IS NOT TRUE, when the intended complement includes them.
ACS: a negative integer may be a code rather than a quantity
The Census Bureau’s American Community Survey publishes numeric codes and paired annotations. Keep E with EA for the estimate, and M with MA for its margin of error. This dictionary is producer specific; it does not turn every negative number into a missing value.
Six documented value / annotation pairs
Estimate
E
Raw value
-666666666
Annotation
-
Estimate cannot be computed. The note lists insufficient observations, some ratios of medians in open-ended intervals and, for a five-year median, a margin of error greater than the median.
Estimate or margin
E / M
Raw value
-999999999
Annotation
N
Estimate or margin cannot be displayed: too few sample cases in the selected geographic area.
Estimate or margin
E / M
Raw value
-888888888
Annotation
(X)
Estimate or margin is not applicable or not available. This code alone does not distinguish those causes.
Margin of error
M
Raw value
-222222222
Annotation
**
Margin of error cannot be computed: insufficient observations. This status does not mean the estimate equals zero.
Margin of error
M
Raw value
-333333333
Annotation
***
Margin cannot be computed: median lies in the lowest or highest open-ended interval.
Margin of error
M
Raw value
-555555555
Annotation
*****
Margin is inappropriate: estimate is controlled to an independent population or housing estimate. In this specific case the Census says the margin may be treated as zero.
A null annotation is not a null estimate
The Census note reproduces this ACS 2024 comparison-profile example for the United States (us = 1). Values below are transcribed from that documentation, consulted on 28 October 2026; no data query was rerun for this record.
Estimate in the published example
CP02_2024_001E
"132737146"
Household count in this 2024 documentation example, not a recalculated current total.
Annotation of that estimate
CP02_2024_001EA
null
The null belongs to EA. E has a value: do not replace the household count with a missing value.
Comparison with 2023
CP02_2024to2023_001SS
"*"
In comparison profiles a single star indicates a statistically significant difference at the 90% confidence level from the most current year. It gives neither the direction nor the size of the difference.
One star in SS and five stars in MA mean different things. The column name is part of the symbol’s meaning.
Three treatments of one small fictional dataset
Teaching example separate from ACS data: three expected quantities, 10, 20 and missing-value code -999999999. Only two quantities are available. This illustrates code handling; it does not propose averaging income medians.
Code treated as a quantity
-333333323
(10 + 20 − 999999999) / 3
The arithmetic runs, but its input is misinterpreted. The result is not a mean of three observed quantities.
Missing value replaced with zero
10
(10 + 20 + 0) / 3
The result of 10 assumes zero imputation. That is not merely data cleaning.
Mean of available values
15
(10 + 20) / 2
The mean of 15 describes two observations out of three expected. It is not automatically the population mean.
Coverage: 2 available values / 3 expected. Retain the third row and its reason for being missing in the record.
Should all negative numbers become missing values?
No. A balance of -7 can be a valid observation if the variable permits it. Target documented codes, relevant columns and their version. Keep an unknown status when a code or annotation contradicts the dictionary, then check the source.
Implication for the record
Apply the code to the right field and product. An unavailable margin does not automatically erase the estimate; an empty annotation does not by itself validate a value. Retain vintage, geography, variable, raw value and annotation.
These observations compare percentage-point and relative changes in a published series. The three countries are selected for reading, not as a digital-performance ranking.
The indicator measures the share of the population using the Internet in the previous three months. It measures neither connection time, access quality nor AI-assistant use.
IT.NET.USER.ZS · Extracted · Latest update reported by the API .
Share of population · % · axis from 0 to 100 · FranceGermanySpain
Read a real series: Internet use, 2020–2024 · %
Country
2020
2021
2022
2023
2024
France
84.71
86.10
85.33
86.84
88.65
Germany
89.81
91.43
91.63
92.48
93.50
Spain
93.21
93.90
94.49
95.45
95.76
France: from 84.71% in 2020 to 88.65% in 2024; 3.95 percentage points, or 4.66% relative to the starting value.
Table values are rounded to two decimals; calculations use the received values before that rounding. Examine revisions and collection differences before making a causal explanation.
Source: ITU, distributed by the World Bank. Extract and chart by Alexis Roux ZG. CC BY 4.0
A table contains empty cells, stripes and special codes. Their meaning must be identified before calculating an average or counting the available observations.
Updated on .
Priority checks
Check the documentation, inventory the methods and count their frequency. Keep the differences between not observed, not applicable, confidential and zero when the source provides them.
Interpretation pitfalls
Replacing all absences by zero can significantly change an indicator. Removing the related lines can also introduce a difficult selection to see.
Working record to retain
The missing values report documented codes, meaning, effectiveness and processing rules. The results indicate how many observations have been used.