Exceljet

Quick, clean, and to the point

Excel N Function

Excel N function
Summary 

The Excel N function returns a number when given a value. The N function can be used to convert TRUE and FALSE to 1 and 0 respectively. When given a text value, the N function returns zero.

Purpose 
Convert a value to a number
Return value 
A number or error code
Syntax 
=N (value)
Arguments 
  • value - The value to convert to a number.
Version 
Usage notes 

Use the N function to convert value to a number. The N function takes one argument, value, which can be a cell reference, a formula result, or a hardcoded value. Values are converted as shown below. The logical values TRUE and FALSE are converted to 1 and 0, and text values are converted to zero. Numeric values and errors are unaffected.

Input value Return value
Any number Same number
A recognized date A date in Excel serial number format
TRUE 1
FALSE 0
Error (#VALUE, #N/A, #NUM!, etc.) Same error code
Other values 0

The N function is provided for compatibility with other spreadsheet programs. In most cases, using the N function is unnecessary, because Excel automatically converts values when needed. However, the N function is a simple way to convert TRUE and FALSE to their numeric equivalents, 1 and 0, as mentioned below.

Examples

The N function converts text values to zero:

=N("apple") // returns 0

Numeric values and errors are not affected:

=N(100) // returns 100
=N(5/0) // returns #DIV/0!

The N function converts TRUE to 1 and FALSE to zero:

=N(TRUE) // returns 1
=N(FALSE) // returns 0
=N(3>1) // returns 1
=N(3<1) // returns 0

There are several ways to perform this conversion in Excel, which is useful in more advanced formulas. For example, the formula below will return a count of cells in a range that contain more than 100 characters:

=SUMPRODUCT(N(LEN(range)>100))

This article explains other ways to convert TRUE and FALSE to 1 and 0.

Notes

  1. The N function removes text values. The T function removes numeric values.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.