ISNA in WPS Spreadsheet: NA Diagnostics, Lookup Caveats and Missing-Value Counts
Use WPS ISNA to detect actual #N/A values, preserve numeric lookup results, and separate unavailable data from other errors and text labels.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: ISNA(value) returns TRUE only when the supplied value is the actual #N/A error. It returns FALSE for other errors and for ordinary values, including text that merely spells #N/A. The Boolean result describes an error type; it does not establish why that error occurred.
Availability and version history
- Introduced: documented by 2019-05-14 in official WPS material; original introduction is not established.
- Availability: Windows Personal is named in the dedicated lesson metadata.
- Scope: exact current supported builds remain unverified; this evidence does not establish equivalent behavior across every WPS client.
- Checked: October 14, 2026 against complete function-specific documentation. Examples are independently modeled; no native WPS test was performed.
Syntax and first controls
=ISNA(value)
=ISNA(NA())
=ISNA("#N/A")
=ISNA(1/0)
The last three controls have expected results TRUE, FALSE and FALSE. NA deliberately creates the actual error; the quoted label is text, while division by zero is a different error.
Examples use English function names and comma separators. They are independent expectations from the documented rule, not recorded native WPS results.
Example 1: classify a mixed results column
Enter these inputs in A2:A9, then fill =ISNA(A2) down B2:B9:
| Input in A | Expected ISNA |
|---|---|
12 |
FALSE |
=NA() |
TRUE |
18 |
FALSE |
="#N/A" |
FALSE |
0 |
FALSE |
=FALSE() |
FALSE |
="" |
FALSE |
=1/0 |
FALSE |
Exactly one row is selected. There is also another actual error in the last row, which ISNA intentionally does not select. A count of one is therefore not the total count of all calculation errors.
Example 2: preserve the original result when it is available
In C2 enter and fill down:
=IF(ISNA(A2),"Unavailable",A2)
Expected selected outputs are 12, Unavailable, 18, the text #N/A, 0, FALSE, empty text and the actual #DIV/0! error. Other errors are not silently replaced by this #N/A-specific rule.
The separately documented IFNA expresses this same fallback purpose directly. A plain ISNA call, however, produces only TRUE or FALSE; it does not preserve the original number by itself.
Advanced warning: wrapping a lookup changes all its results
The WPS lesson demonstrates wrapping a lookup with ISNA to turn a #N/A result into TRUE. That is useful as a diagnostic flag, but it changes successful numeric results into FALSE as well.
For a controlled original result column containing 12, an actual #N/A and 18, applying ISNA produces FALSE, TRUE and FALSE. Those are flags, not the original quantities. They cannot be used as a replacement quantity column without changing the meaning of its total.
Keep the lookup result in one column and the ISNA flag beside it. If a display replacement is needed, use the explicit conditional pattern above and retain the source result for inspection.
Advanced example: distinguish failure type from failure cause
A #N/A can arise because a lookup did not find a key. It can also already exist in the value stored for a successfully matched record. ISNA applied to the resulting value sees the same error type in both cases.
Therefore, a label such as “Key missing” requires additional evidence about whether a match actually occurred. The safer label Unavailable describes what this test has established. This guide does not certify every WPS lookup mode, tie rule or source-error propagation matrix from the ISNA lesson alone.
Use the dedicated VLOOKUP or MATCH guide when designing the independent lookup check, and confirm the intended mode against its product-specific reference.
Advanced check: reconcile three diagnostic counts
Across the eight inputs in the first fixture:
=IF(ISNA(A2),1,0)selects 1 row.=IF(ISERR(A2),1,0)selects 1 row.=IF(ISERROR(A2),1,0)selects 2 rows.
The first two categories are disjoint, and their counts add to the all-error count for this fixture. The text label #N/A contributes to none of those counts.
If a clean three-row numeric report replaces only an actual #N/A with zero, the values 12, #N/A and 18 become 12, 0 and 18. The total is 30, but the all-row average is 10 rather than the known-observation average of 15. A fallback policy affects denominators as well as display.
Limits and related tests
The source defines the #N/A test, not a general parser for messages, a proof of missing keys or a guarantee of lazy branch evaluation. Arrays, unselected failing expressions and every newer error type remain outside these scalar controls.
Use ISERROR for the broader error category and ISNUMBER for numeric type. A FALSE ISNA result alone certifies neither of those properties.
References and verification
- WPS Academy: ISNA, 2019-05-14 (Chinese; edition scope as stated above).
- WPS Academy: shared IS-function syntax and nonconversion rule, May 14, 2019 (Windows Personal).
- Editorial testing standards.
The source establishes the cited product behavior and documented history, not a native-tested current release. Additional linked functions retain their own evidence scopes.