N in WPS Spreadsheet: Value Conversion, Boolean Indicators and Text Pitfalls
Understand WPS N conversion with an explicit type table, Boolean indicators, numeric-text controls and error propagation, without guessing date-system details.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: N(value) converts a supplied value according to its type. The official WPS table says numbers remain unchanged, TRUE becomes 1, FALSE becomes 0, dates return their serial numbers, errors remain errors, and other values become 0. Numeric-looking text is therefore not parsed as a number by N.
Availability and version history
- Introduced: documented by 2019-06-25 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 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 the conversion rule
=N(value)
The one argument is the value or cell reference to convert. The dated WPS lesson explains that many ordinary calculations already perform their own conversions; N is a specific compatibility function, not a universal prerequisite for arithmetic.
Examples use English function names and comma separators. They are independently modeled expectations based on the documented rule, not captured results from an installed WPS build.
Example 1: use a controlled type table
Enter the following in A2:A8. Formulas beginning with an equals sign generate the stated type explicitly; keep B for outputs.
| Cell | Input entered in A | Stored value or type | Expected =N(A2) filled down |
|---|---|---|---|
| A2 | 12 |
Number | 12 |
| A3 | -3.5 |
Number | -3.5 |
| A4 | =TRUE() |
Boolean | 1 |
| A5 | =FALSE() |
Boolean | 0 |
| A6 | ="12" |
Text | 0 |
| A7 | ="Pending" |
Text | 0 |
| A8 | ="" |
Empty text | 0 |
The seven converted values total 9.5. That total follows N’s conversion policy; it is not a validated total of the original business observations. In particular, the numeric-looking text 12 contributes zero, while the Boolean TRUE contributes one.
A format that makes A6 look like a number does not demonstrate that the stored text has changed type. Use ISNUMBER to examine the original value before deciding whether any conversion is appropriate.
Example 2: convert simple comparison results into indicators
Enter numeric scores 59, 60, 75 and 90 in D2:D5. In E2 enter and fill down:
=N(D2>=60)
Expected indicators are 0, 1, 1 and 1, totaling 3. Each comparison first produces a Boolean; N then applies its documented TRUE/FALSE conversion. The proportion meeting this threshold is 3/4, or 0.75, because all four supplied scores are valid observations in this fixture.
The explicit alternative =IF(D2>=60,1,0) produces the same four indicators. Neither formula defines how missing, erroneous or numeric-looking text scores should be handled. Those need a separate input policy rather than silent replacement with zero.
Advanced example: compare conversion with numeric validation
For A2:A8 from the first table, put this in C2 and fill down:
=IF(ISNUMBER(A2),A2,0)
Under the documented numeric-type test, expected outputs are 12, -3.5, 0, 0, 0, 0 and 0, totaling 8.5. This explicit policy accepts genuine numbers and supplies zero for the other non-error controls. It differs from N’s 9.5 because the Boolean TRUE is not a genuine numeric input.
If A6 was meant to represent a measured quantity of 12, both totals have lost that intended quantity. Neither is a text parser. Inspect the input convention and use a deliberate conversion such as VALUE only when the text has an unambiguous numeric meaning in the workbook’s locale.
Advanced check: errors are not converted to zero
=N(1/0)
=N(NA())
=N("#N/A")
The documented expectations are #DIV/0!, #N/A and 0. The first two inputs are actual errors; the third is ordinary text spelling an error label. The conversion table explicitly preserves an error value.
Consequently, N is not an error handler. If =IFERROR(N(A2),0) is appropriate for a particular report, its fallback policy should be explained: an error becomes zero because IFERROR selected that replacement, not because N made the source valid. Keeping an error flag beside the report can prevent missing observations from becoming invisible.
Dates, comments and important limits
A date stored as a spreadsheet number returns its underlying serial value. The source’s prose about the 1899 origin and the 2008 day count is internally inconsistent, so this guide does not certify an absolute date serial or workbook date-system boundary from that paragraph. Inspect the actual workbook settings and source value before moving serial dates between files.
For a harmless literal control, =100+N("Checked by reviewer") has expected result 100, since the text contributes zero. The formula does not prove that anyone checked the value; an embedded comment is only text. Do not mistake such a label for an audit record or approval.
This guide does not establish every reference-array, empty-reference or locale coercion edge case. Use IF for a stated decision, IFERROR for an explicit error fallback, and N only when its particular type conversion is the intended operation.
References and verification
- WPS Academy: N, 2019-06-25 (Chinese; Windows Personal scope).
- 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.