NA in WPS Spreadsheet: Explicit Missing Values, Error Propagation and Report Controls
Create an actual WPS #N/A error with NA(), distinguish unavailable observations from zero and text, and preserve meaningful denominators in a controlled report.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: =NA() returns the actual #N/A error, indicating that a valid value is unavailable. It takes no arguments, but its parentheses are required. An unavailable observation is different from a measured zero, an empty-looking cell or text that merely says #N/A.
Availability and version history
- Introduced: documented by 2019-06-10 in the official WPS lesson; original introduction is not established.
- Availability: The WPS lesson documents this function, but its platform and edition field is blank.
- Scope: exact current supported builds remain unverified; the dated lesson does not establish equivalent availability across all WPS clients.
- Checked: October 14, 2026 against function-specific documentation. Examples are independent expectations; no native WPS test was performed.
Syntax and first distinctions
=NA()
="#N/A"
=0
=""
These produce, respectively, an actual error, ordinary text, a number and empty text. The first two can look similar on a worksheet while behaving differently in later formulas. The official WPS lesson also describes entering #N/A directly, but NA() makes the intended error-producing formula explicit.
Examples use English function names and comma separators. The source provides product-specific semantics; outputs here are independently modeled expectations, not native application test results.
Example 1: mark an unavailable measurement explicitly
Enter this small report in A1:B5. The formulas in column B are actual worksheet inputs.
| A · Observation | B · Value entered |
|---|---|
| First | 10 |
| Second | =NA() |
| Third | 20 |
| Fourth | =NA() |
The source explains that formulas referring to an actual #N/A value can return that error. For a direct arithmetic control:
=B2+B3
The expected result is #N/A, rather than 10 or a silently adjusted sum. The unavailable second observation remains visible as a problem in a calculation that requires it.
Replacing B3 with zero changes the meaning: =B2+B3 would then be 10, because the report now claims a numeric zero. That replacement is only valid if zero is genuinely the intended observation or an explicitly chosen fallback.
Example 2: build a separate presentation column
Keep the original values in B2:B5. In C2 enter and fill down:
=IFNA(B2,"Unavailable")
Expected displayed values are 10, Unavailable, 20 and Unavailable. IFNA replaces the specific #N/A error; it is not a general assertion that the record was absent or that every other input is valid.
For comparison, =IFNA("#N/A","Unavailable") returns the original text #N/A. It does not select the fallback because a string spelling the error is not the actual error value. Likewise, a real zero remains 0.
The raw column retains the missing-value evidence. Keep labels and numeric results in a clearly identified output column so another reader does not mistake text for a usable measurement.
Advanced example: make the denominator explicit
In D2 enter =IFNA(B2,0) and fill through D5. Expected numeric outputs are 10, 0, 20 and 0, totaling 30. Dividing this total by all four rows gives 7.5.
That is a zero-imputed average, not the average of the two known measurements. To report the known numeric observations separately, place this in E2 and fill down:
=IF(ISNUMBER(B2),1,0)
Expected indicators are 1, 0, 1 and 0, totaling 2. For this fixture, the known-observation average is 30/2, or 15. Report the accompanying coverage 2 of 4 so the average does not conceal the two unavailable measurements.
The zero-filled helper and numeric-count helper are deliberately separate. Their correct combination depends on this bounded dataset, which contains numbers and #N/A only. A different error or an unexpected text value requires its own review; these helpers are not a complete data-cleaning system.
Advanced check: stop when no observations are numeric
If B2:B5 all contain =NA(), the numeric-count indicators total 0. Dividing by that count is no longer a valid average. If G2 contains the numeric total and G3 contains the numeric count, express the reporting rule explicitly:
=IF(G3=0,"No numeric observations",G2/G3)
The intended selected result is No numeric observations when the count is zero; with total 30 and count 2, the selected result is 15. This is a documented IF selection rule and an independent model, not a certification of every internal branch-evaluation edge case.
A report can therefore distinguish three states: a known zero measurement, at least one known measurement with a calculated average, and no numeric observations. Collapsing all three into the same displayed zero loses useful information.
Chart behavior and other limits
The WPS lesson demonstrates using NA in a particular line chart to change how a missing segment is displayed. That example does not establish a universal rule for every chart type, empty-cell setting, client or release. Check the specific chart and its configuration before relying on gaps, connections or skipped points.
The lesson’s edition field is blank. This guide therefore does not infer a historical Windows, mobile or online build from its screenshots or from other products’ behavior. It documents the function and the stated examples while keeping exact client availability unresolved.
Use ISNUMBER to test numeric type, IFNA for a specific #N/A fallback and IFERROR only when replacing other errors is also part of the intended policy.
References and verification
- WPS Academy: NA, 2019-06-10 (Chinese; edition field blank).
- WPS Academy: numeric-type testing.
- WPS Academy: specific #N/A handling.
- Editorial testing standards.
Worked outputs are documentation-based independent models. Source date and stated edition establish the cited scope, not a tested current build.