Why does N(TRUE) return 1 and N(FALSE) return 0?

In spreadsheets, TRUE and FALSE are boolean values. N() converts them to their numeric equivalents: TRUE becomes 1 (representing an on/active state), FALSE becomes 0 (representing an off/inactive state). This is standard in most spreadsheet systems and programming languages.

What happens when N() receives text that is not a number?

N() returns 0 for most non-numeric text. For example, N('apple') returns 0. If the text contains an error (like the result of a failed VLOOKUP), N() returns that error instead. To handle text carefully, pair N() with IFERROR().

Can N() convert currency text like '$50.00' to a number?

No, N() cannot parse currency symbols, commas, or percentage signs. It will return 0. If you need to extract numbers from formatted text, use SUBSTITUTE() to remove the symbols first: =VALUE(SUBSTITUTE(A1, '$', ''))

Why would I use N() instead of just referencing the cell directly?

N() is useful when you're unsure of a cell's format or want to guarantee a numeric result before calculations. For instance, if a column contains a mix of numbers and text, N() ensures a numeric output or 0, preventing unexpected calculation errors or text-in-numbers warnings.

N function

N converts a value to a number, returning the numeric equivalent of dates, booleans, and numeric text strings.

=N(value)

Generate a N formula

Describe what you need. The generator will reach for N where N is the right tool, and tell you when it is not.

Arguments

How N reads its arguments

Argument	Required	Description
value	Required	Any value: a number, text, date, boolean, reference to a cell, or formula. N attempts to extract its numeric representation.

Returns

A number. For dates, the date serial number; for TRUE/FALSE, 1 or 0; for text numbers, the numeric value; for non-numeric text or empty cells, 0.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Extract quantity as a number for calculations

Ingredient	Supplier	Unit	Qty	Expiry Date
Chicken Breast	Fresh Farms	kg	45	2026-09-20

=N(D2)

Result: 45

The quantity in D2 is stored as text '45'. N() converts it to the number 45, ensuring it can be used in arithmetic operations like SUM or AVERAGE. Without N(), text-formatted numbers might not calculate correctly in some contexts.

2. Convert expiry date to its serial number for date math

Ingredient	Supplier	Unit	Qty	Expiry Date
Olive Oil	Mediterranean Co	L	12	2027-03-15

=N(E2)

Result: 46379

Dates are stored internally as serial numbers. N(E2) returns 46379, the serial number for 2027-03-15. This is useful for date arithmetic: subtract one date serial from another to find the number of days between expiry dates in your inventory.

3. Convert low-stock alert from TRUE/FALSE to 1/0 for counting

Ingredient	Supplier	Unit	Qty	Expiry Date
Tomatoes	Local Market	kg	30	2026-09-18

=N(IF(D2<20, TRUE, FALSE))

Result: 0

The IF formula returns FALSE (because 30 is not less than 20). N() converts FALSE to 0. If quantity were under 20, IF would return TRUE, and N() would return 1. This numeric format is easier to sum across rows to count how many items need reordering.

Common errors

Which N error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	N() receives an error value from another formula, such as =N(VLOOKUP(item, range, 2)) when the lookup fails and returns #N/A or another error.	Wrap the formula in IFERROR or IFNA to handle missing values before N() receives them: =IFERROR(N(VLOOKUP(...)), 0)
#REF!	N() references a cell that was deleted or moved, such as =N(D2) after column D was removed from the sheet.	Correct the cell reference to point to a valid cell, or undo the deletion to restore the referenced cell.
#N/A	N() references a cell containing #N/A from a failed lookup, such as =N(XLOOKUP(item, range, result_range)) when no match is found.	Use IFNA to catch #N/A before passing to N(): =IFNA(N(XLOOKUP(...)), 0)

Tips and when to use something else

N() always attempts conversion—use TYPE() or ISNUMBER() instead if you only want to check a value's type, not convert it.
For text strings, N() returns 0 in most spreadsheet implementations; to reliably parse text numbers, verify the source doesn't contain spaces or invisible characters.
Dates are a powerful use case: N() reveals the underlying serial number, which makes date subtraction simple (e.g., =N(expiry_date)−N(today) gives days remaining).
N() and ISNA() work well together: use ISNA() to detect #N/A errors before they reach N(), avoiding cascading errors in calculations.

Related functions

ISNUMBERChecks whether a value is a number, returning TRUE for numeric values and FALSE for text, dates, blanks, and errors.TYPEReturns a number indicating the data type of a value: 1 for numbers, 2 for text, 4 for logical values, 16 for errors, and 64 for arrays.ISNAISNA detects whether a value is the #N/A error, typically returned when a lookup function fails to find a match.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

Why does N(TRUE) return 1 and N(FALSE) return 0?: In spreadsheets, TRUE and FALSE are boolean values. N() converts them to their numeric equivalents: TRUE becomes 1 (representing an on/active state), FALSE becomes 0 (representing an off/inactive state). This is standard in most spreadsheet systems and programming languages.
What happens when N() receives text that is not a number?: N() returns 0 for most non-numeric text. For example, N('apple') returns 0. If the text contains an error (like the result of a failed VLOOKUP), N() returns that error instead. To handle text carefully, pair N() with IFERROR().
Can N() convert currency text like '$50.00' to a number?: No, N() cannot parse currency symbols, commas, or percentage signs. It will return 0. If you need to extract numbers from formatted text, use SUBSTITUTE() to remove the symbols first: =VALUE(SUBSTITUTE(A1, '$', ''))
Why would I use N() instead of just referencing the cell directly?: N() is useful when you're unsure of a cell's format or want to guarantee a numeric result before calculations. For instance, if a column contains a mix of numbers and text, N() ensures a numeric output or 0, preventing unexpected calculation errors or text-in-numbers warnings.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17