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.
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
To count visible columns in a range, you can use a helper formula based on the CELL function with IF, then tally results with the SUM function. In the example shown, the formula in I4 is:
=SUM(key)
where "key" is the...
To get INDEX to return an array of items to another function, you can use an obscure trick based on the IF and N functions. In the example shown, the formula in E5 is:
=SUM(INDEX(data,N(IF(1,{1,2,3}))))
where "data"...
To leave a comment in a formula that returns a numeric result, you can use the N function. In the example shown, the formula in F9 is:
=SUM(F5:F8)+N("Q4 numbers are estimates")
How this formula works
This...
To translate text values into numbers and sum the result, you can use an INDEX and MATCH formula, and the SUM function. In the example shown, the formula in H5 is:
{=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0)))))}...
To count dates in the current month, you can use a formula based on the COUNTIFS or SUMPRODUCT function as explained below. In the example shown above, the formula in E7 is:
=COUNTIFS(dates,">="&...
To count cells that contain more than a certain number of characters you can use a formula based on the SUMPRODUCT, LEN, and N functions. In the example shown, the formula in C2 is:
=SUMPRODUCT(N(LEN(B5:B11)>100...
Related functions
Good links
