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. Values are converted according the the following table:
| Input value | Return value |
|---|---|
| Any number | Same number |
| A recognized date | A date in Excel serial number format |
| TRUE | 1 |
| FALSE | 0 |
| An error code (#VALUE, #N/A, etc) | Same error code |
| Other values | 0 |
In most cases, using the N function is unnecessary, because Excel automatically converts values when needed.
The N function is provided for compatibility with other spreadsheet programs.
N formula examples
The heart of this formula is a basic INDEX and MATCH formula, used to translate text values into numbers as defined in a lookup table. For example, to translate "EX" to the corresponding number, we would use:
=INDEX(...
Working from the inside out, the LEN function runs on the range B5:B11. Because we give LEN multiple values, it returns multiple results in an array like this:
{127;78;43;112;59;72;154}
Which is evaluated against the...
It is surprisingly tricky to get INDEX to return more than one value to another function. To illustrate, the following formula can be used to return the first three items in the named range "data", when entered as a...
This is a tricky use of N() that allows you to use it as a way to leave in-cell comments. It only works for formulas that return numeric results.
The N function takes a value and returns a number. When given a text...
There is no direct way to detect a hidden column with a formula in Excel. You might think of using the SUBTOTAL function, but SUBTOTAL only works with vertical ranges. As a result, the approach described in this...
At the core, this formula uses the COUNTIFS function to count dates that are greater than or equal to the first day of the current month, and less than the first day of the next month. The EOMONTH function is used to...
Related functions
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
