IFNA in WPS Spreadsheet: Handle #N/A Without Hiding Other Errors

• Introduced: documented by 2019-05-13 in the official WPS lesson; original introduction is not established.
• Availability: WPS Office for Windows Personal; no numeric release is stated in the edition metadata.
• Scope: exact current supported builds remain unverified. The Windows lesson does not establish equivalent availability in other WPS clients.
• Checked: October 14, 2026 against function-specific documentation. Examples were independently calculated; no native WPS test was performed.

=IFNA(value, value_if_na)

Both arguments supply the expression to inspect and its #N/A fallback. Examples use English function names and comma separators. To create an actual unavailable-value error for a control, the official WPS NA lesson documents =NA() with no arguments.

=IFNA(NA(),"Unavailable")
=IFNA(10/2,"Unavailable")

Expected results are Unavailable and numeric 5. The first fallback does not recover the missing value; it deliberately changes how that error is represented.

=IFNA(10/0,"Unavailable")
=IFNA("#N/A","Unavailable")
=IFNA(0,"Unavailable")
=IFNA(FALSE(),"Unavailable")

Expected results are #DIV/0!, the text #N/A, numeric 0 and logical FALSE. Division by zero remains visible because it is not #N/A. The IFERROR lesson independently documents that division error and the broader error-handling alternative.

Do not create the text control by assuming that typing an error-looking label always stores text. The quoted formula shown above explicitly creates a string, while NA() explicitly creates the error.

In A2:A4 enter numeric 12, =NA() and numeric 18. In B2 and C2 enter these formulas and fill down:

=IFNA(A2,0)
=IFNA(A2,"0")

Column B has expected values 12, numeric 0 and 18. Column C has 12, text 0 and 18. Both may look similar on screen, but the quoted fallback has a different stored type.

Under the separately documented AVERAGE reference rule, B averages to 10 using three numbers; C averages to 15 using two numbers and ignoring referenced text. Both numeric totals are 30, so checking only a total would miss the denominator change.

The IFNA lesson’s walkthrough uses a quoted zero. This guide does not silently rewrite that quoted string into a numeric zero or claim that hiding the error necessarily makes every later calculation correct.

Use =IFNA(A2,"Unavailable") on the same three inputs. The resulting 12, Unavailable and 18 make the missing observation easier to see than a replacement zero.

Whether a summary should exclude, replace or reject that observation depends on the reporting policy. Neither a mean of 10 nor 15 reconstructs the missing measurement. Retain the original error-bearing column when auditing the decision.

For a typed check of the two zero fallbacks, =ISNUMBER(B3) has expected TRUE and =ISNUMBER(C3) FALSE under the official WPS ISNUMBER lesson, which explicitly does not convert numeric text during its test.

The WPS lesson demonstrates IFNA around a lookup that cannot find a requested item. That is one source of #N/A, not the only possible one.

For example, enter a known record identifier in E2 and =NA() in its value cell F2. =IFNA(F2,"Unavailable") returns the fallback even though the record exists. A lookup that successfully returns that same unavailable source value can lead to the same final fallback.

Therefore label the output Unavailable unless a separate existence check proves that the key itself is missing. IFNA classifies the resulting error; it does not inspect the full provenance of that error or certify the lookup table’s completeness.

Leave H2 genuinely empty:

=IFNA(H2,"Fallback")
=IFNA(NA(),H2)

The lesson says an empty cell supplied as either argument is treated as empty text, so both have expected result empty text. The output cell still contains a formula. This is different from a genuinely empty cell and from omitting an argument.

A formula returning "" remains information under COUNTA’s documented rule. Do not judge the stored state solely from the absence of visible characters.

Use IFERROR only if every handled error class genuinely warrants the same fallback. Use IFNA when the narrower #N/A distinction is intended. Neither function is a general validation or missing-data repair tool.

The captured IFNA source describes array results, but these examples are scalar controls. Automatic spill, every array-entry context, unselected-argument evaluation and complete cross-client coercion remain outside this documentation-based guide.

• WPS Academy: IFNA lesson, 2019-05-13 (Chinese; Windows Personal scope).
• Editorial testing standards.

Worked outputs are independently calculated expectations, not captured native results. The dated source establishes the stated product scope, not every current client or build.