In-depth practical guides

Read missing values and statistical flags

Separate zero, absence, confidential, estimated and provisional values before calculations and charts.

Updated :

Statistical analysis workstation with illustrative charts.

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.

  1. Read the exact legend

    Find the code list and structure version. Keep value, status, unit and note in separate fields.

  2. Classify absences

    Separate not collected, not applicable, unpublished, confidential and extraction errors. Preserve an unknown cause when undocumented.

  3. Define the calculation

    State expected population and usable observations. Never silently replace missing with zero; explain exclusions and imputations.

  4. 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
ObservationUseful checkNext action
72 usable values720 / 72 = 10 units.Publish the available-value mean with its count: 72 of 100.
18 missing valuesTheir values and reasons for absence need examination.Do not automatically replace them with zero.
10 confidential valuesWithholding 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
ItemValueContext
A10Observed
B0Observed
CBlankNon-response
D20Observed
EConfidentialMasked

Your checks

  1. Separate both missing states from observed zero.
  2. Calculate the available-value mean.
  3. 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.

Documentation reference : W3C — Tabular data model, null values · Consulted on

Record to retain

  • File, release and flag legend.
  • Raw values, codes and known causes.
  • Expected and usable counts.
  • Exclusion, imputation and rounding rules.
  • Variable-specific denominators, coverage and visual distinction between observations and estimates.

Official references

Related source profiles

Related protocols

Checks before reaching a conclusion

  • Are zero and missing distinct?
  • Does the legend match this file?
  • Is the denominator shown?
  • Do flags survive export?

Frequently asked questions

Should incomplete rows be removed?

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.

  • Access date and time zone
  • Exact URL
  • Dataset / series code
  • Exact column name
  • Unit / calculation basis
  • Missing values and nullability
  • Observed status
  • Sample / population
  • Method / definition
  • Version / revision
  • Limit / uncertainty
  • Decision / next action
  • Review owner
  • Next review

DOCUMENTED REFERENCE

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
ReferenceValue or formWhat it identifies
bOBS_STATUSTime-series break. Read metadata before calculating change across it; the code alone establishes neither cause nor size.
dOBS_STATUSDifferent definition: consult metadata. An identical variable label does not establish comparable observations.
pOBS_STATUSProvisional value. Retain extraction date and status to distinguish later revisions from economic change.
iOBS_STATUSValue imputed by Eurostat or another receiving agency, rather than identified as directly collected.
mOBS_STATUSMissing value: data cannot exist. Neither measured zero nor an absence whose cause may be invented.
bdipOBS_STATUSPublished combination: break, different definition, imputation and provisional value. Retain the full code, not its first letter.
CCONF_STATUSConfidential, in CONF_STATUS. Migration documentation replaces the former dissemination c with this code.
NCONF_STATUSNot for publication. An unpublished figure does not establish zero activity.
PCONF_STATUSInformation 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.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

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
ReferenceValue or formWhat 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.
datatypestring / integer / date …Datatype controls conversion. Invalid non-null integer text is an error, not zero.
requiredrequired = trueRequired 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.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

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
ReferenceValue or formWhat it identifies
NULL = NULLNULLNULL = NULL is unknown, not proof of equality. column = NULL is not a missingness test.
NULL IS NULLTRUEIS NULL tests absence, not the four-character string NULL.
NULL IS NOT DISTINCT FROM NULLTRUEIS 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 NULLFALSEFALSE AND unknown is FALSE; this does not guarantee left-to-right evaluation.
TRUE OR NULLTRUETRUE OR unknown is TRUE; replacing every unknown with false does not reproduce complex logic.
NOT NULLNULLNOT unknown remains unknown; negation need not recover every discarded row.
WHERE conditionTRUEWHERE 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.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

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.

Primary documentation

Documentation consulted on .

DOCUMENTED REFERENCE

Read a real series: Internet use, 2020–2024

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 100Chart of three series; every value is available in the following table.025507510020202021202220232024FranceGermanySpain
Share of population · % · axis from 0 to 100 · FranceGermanySpain
Read a real series: Internet use, 2020–2024 · %
Country20202021202220232024
France84.7186.1085.3386.8488.65
Germany89.8191.4391.6392.4893.50
Spain93.2193.9094.4995.4595.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

Consult the official series · Definition and methodology

DEEPER READING / PRACTICE

Distinguish missing, zero and not applicable

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.