N

What it does

N(value)

N(value) maps a value to a number by a fixed table. It is a total function on the scalar kinds — it always produces something, and for one class of input that something is a silent zero.

Microsoft's table, verbatim:

Two rows carry all the risk.

"Anything else" is 0, and it includes text that reads as a number. N("19") is 0, not 19. N is not a parser. This is the single fact that makes N dangerous in a formula that was meant to salvage imported data: it converts every text cell — meaningful or not, numeric-looking or not — to zero, with no error and no signal.

The date row is not a special case. A date is a number in Excel's value universe (the value universe); the built-in date format is presentation. The row exists in Microsoft's table because a reader thinks of a date cell as holding a date, but mechanically it collapses into the first row. What N returns for a date is the serial number that was already there.

Errors pass through unchanged. N is not an error-masking function. N(#DIV/0!) is #DIV/0!, documented explicitly.

Microsoft's own remark on the function's purpose is worth quoting because it sets expectations correctly: "It is not generally necessary to use the N function in a formula, because Excel automatically converts values as necessary. This function is provided for compatibility with other spreadsheet programs."

Arguments

value — required, exactly one. The published signature is N(value).

Every scalar kind is admissible; none is rejected. The interesting positions are the ones Microsoft's table does not name:

• An empty cell. Falls under "anything else" — 0. It is the same output as text, which is why N(A1)=0 cannot distinguish a blank from a label.

Try it

Quirks

Result and edge cases

Returns a Number, an Error (when the input was an error), or an array of those.

• No conversion failure exists. N never returns #VALUE! for a value it can be given. Where VALUE("abc") errors, N("abc") is 0. That difference — error versus silent zero — is the whole reason to prefer VALUE when you actually want a parse.
• Text is always 0, regardless of content, including the empty string and including "TRUE".
• Logicals convert, unlike in most range-scan contexts where logicals are skipped. N(TRUE) is 1; this is the documented behaviour and is the usual way to turn a boolean into a summable number, alongside the double-unary --.
• Percentages, currency and times are numbers already; N returns the stored value, so N(50%) is 0.5 and N of a cell showing 12:00 is 0.5 as well.
• Errors propagate rather than becoming a number.

Errors

Every other error on a page featuring N came from somewhere else and was passed through.

How to implement

Notes for implementers

1. Do not implement N by calling the general to-number coercion. The general primitive parses numeric text; N must not. Routing N through it is an easy mistake that makes N("19") return 19 and passes every test written by someone who expected that.
2. Errors must pass through, not be caught. N is not an inspection function; the error row in the table is a pass-through, not a classification.
3. Empty and Missing both land on 0, but they arrive by different routes and should be handled explicitly rather than by falling through a default.
4. The array decision is load-bearing and undocumented. Whichever way an engine goes — elementwise map, or take the first element — it should be recorded as a decision awaiting evidence, because the documentation does not settle it.

Rust code

unary(N, "N", VALUE_OR_REFERENCE, n)

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• VALUE(text) is the function that actually parses. Use it when non-numeric text should be an error rather than a zero.
• NUMBERVALUE(text, decimal_separator, group_separator) is the locale-explicit parser and the right choice for text that came from another locale.
• ISNUMBER is the test that tells you which branch you are in before you commit to N.
• -- (double unary) is the idiomatic modern replacement for N on boolean masks; it coerces through arithmetic, which means it errors on non-numeric text where N returns zero. The two are not interchangeable on messy data.
• SUM and the aggregate family show the same numeric-text asymmetry from the other side: SUM("3") is 3 as a direct argument but a text cell in a scanned range contributes nothing (coercion and lifting). N is stricter than direct argument coercion and matches the range-scan outcome.

Named variants

No named variants are recorded yet.