Excel N Function

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.

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.