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 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...
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 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 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 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 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...
Related functions
Good links
