Excel N Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 60 of 105 · Full Excel course

The Excel N function converts a value to a number: numbers and dates are returned as their numeric value, TRUE becomes 1, FALSE becomes 0, text becomes 0, and an error is passed through unchanged. N in Excel is mostly used to coerce logical results inside array formulas and, as a long-standing trick, to attach a comment to a formula without changing its result.

N syntax

=N(value)

Arguments

Argument Required Meaning
value Required The value or cell to convert.

What N returns

If value is N returns
A number That number
A date or time Its serial number, e.g. 1 Jan 2023 becomes 44927
TRUE 1
FALSE 0
Text (including “123”) 0
An empty cell 0
An error value The same error

Step-by-step example

Cell A1 contains the logical value TRUE.

=N(A1)
  1. Excel reads A1 and finds TRUE.
  2. TRUE maps to 1.
  3. The formula returns 1. With FALSE in A1 it returns 0; with the text Excel it returns 0; with the date 1 January 2023 it returns 44927.

Practical use cases

1. Add a note inside a formula

=SUM(B2:B50)+N("Q1 sales excluding returns")

N of text is 0, so the total is unchanged but the explanation travels with the formula.

2. Coerce logical values in SUMPRODUCT

=SUMPRODUCT(N(A2:A100="East"), C2:C100)

N converts TRUE/FALSE to 1/0 so the multiplication works. The double minus (–) is the more common shorthand.

3. Safe arithmetic on mixed columns

=N(B2)*N(C2)

Text entries such as “n/a” become 0 instead of producing #VALUE!.

4. Convert a date to its serial number for display

=N(D2)

5. Running total that ignores a text header above

=N(C1)+B2

In the first data row C1 is the header text, so N(C1) is 0 and the running total starts cleanly. Fill down.

Common mistakes and errors

  • Expecting N to convert numeric text – N(“123”) is 0, not 123. Use VALUE or NUMBERVALUE for that.
  • Expecting errors to become 0 – N(#DIV/0!) returns #DIV/0!. Wrap in IFERROR if you want 0.
  • Wrong date serial numbers – Excel’s 1900 date system counts from 1 January 1900 as day 1 (with the leap-year bug making 1 March 1900 day 61). 1 January 2023 is 44927, not 44379.
  • Using N on a range in legacy Excel – N(A2:A10) in a single cell returns only the same-row value; use it inside SUMPRODUCT or array-enter.
  • Readability – N is terse. In shared workbooks, — or explicit IF may be clearer to colleagues.

Tips and best practices

  • Use N(“comment”) sparingly and only for short notes; a cell comment is easier for others to find.
  • Prefer — over N() inside SUMPRODUCT in shared workbooks because more people recognise it.
  • Do not use N to clean numeric text; VALUE, NUMBERVALUE or Text to Columns are the right tools.
  • Combine with IFERROR when a column may contain errors that should count as zero.
  • Use N(D2) to reveal a date serial when debugging date arithmetic without changing formats.

Related functions

Frequently asked questions

Can the N function convert text to numbers?

No. N returns 0 for any text, even “123”. Use VALUE(“123”) or NUMBERVALUE to convert numeric text.

What does N return for an error?

The error itself. N(#N/A) is #N/A. Combine with IFERROR, for example IFERROR(N(A2), 0), if you want errors treated as zero.

What is the serial number of a date in Excel?

The count of days since 1 January 1900 in the default date system. N(DATE(2023,1,1)) returns 44927; each following day adds 1, and times are fractions of a day.

Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.

← PreviousExcel MONTH Function: Syntax, Examples and TipsAll functionsExcel Formulas and FunctionsNext →Excel NA Function: Syntax, Examples and Tips