Purpose
Return value
Syntax
=N(value)
- value - The value to convert to a number.
Using the 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.
| 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
- The N function removes text values. The T function removes numeric values.
Purpose
Return value
Syntax
=NA()
Using the NA function
The NA function returns the #N/A error. #N/A means “not available” or “no value available”. You can use the NA function to display the #N/A error when information is missing. Note that if you use the NA function this way, other formulas that depend on cells that contain the #N/A error will also display #N/A, unless you specifically trap and manage the error. The NA function takes no arguments.
Examples
The NA function returns the #N/A error:
=NA() // returns #N/A
You can use the NA function in other formulas. For example, in the formula below, the IF function is configured to test if cell A1 is empty. If so, IF returns NA(), which returns the #N/A error. If A1 is not empty, IF returns A1*B1:
=IF(A1="",NA(),A1*B1) // #N/A if A1 is empty
You can use the NA function to indicate missing information. In the worksheet shown above, cells C9 and C13 contain the NA function:
=NA()
This indicates that cost is not available. In cell D5, the formula copied down is:
=B5*C5 // qty * cost
In cells D9 and D13, the formula returns #N/A because C9 and C13 contain errors. In cell D15 the SUMIF function is used to sum values in column D while ignoring the #N/A error:
=SUMIF(D5:D13,"<>#N/A") // ignore #N/A
If the SUM function was used instead, it would return #N/A:
=SUM(D5:D13) // would return #N/A
Notes
- When other formulas refer to cells that contain #N/A, they also return #N/A.
- NA takes no arguments, but you must provide empty parentheses.
- You can also enter the value #N/A directly into a cell as text.
