Function Reference: N
| Syntax | ||
| =N(value) | ||
| Purpose / Description | ||
| Returns the number value of a value, filtering non-numeric values. | ||
| Arguments | ||
| Name | Type | Description |
| value | any | A constant, reference or nested expression that returns a value from which the number value is returned. |
| Return Values | ||
| Type | Specific Value | Description |
| number | The number value of the value passed to the value argument. If multi-cell range, only the result for the first cell in the range is returned. | |
| number | 1 | If the value of the value argument is the logical value TRUE, 1 is returned. |
| number | If the value of the value argument is the logical value FALSE, or it is a reference to a blank cell, the value 0 is returned. If the value of the value argument is any text string, the value 0 is returned. |
|
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | This function does not accept multi-cell reference. If a multi-cell reference is given, the result is the value. In either dynamic or legacy arrays, a single value is returned, the value of the N of the first item in the array or multi-cell range. | |
| Comment: | It is noteworthy that the expression N(range) returns exactly the same answers as the longer and more common =IF(ISNUMBER(range),range,0), with one exception that N(TRUE) = 1 whereas IF(ISNUMBER(TRUE),TRUE,0) = 0. | |
| Advice: | We use the N function extensively in our workbooks and in the course workbooks for our courses, as it is such a neat solution to a range of problems that would otherwise then require error handling. | |
| Advice: | It is possible to use the N function with a multi-cell range if an nested array expression is used, since the array handling will call the function once for each value. However, the nesting of a more complex expression removes some of the simplicity, in which case the IF(ISNUMBER()) approach mentioned above is probably simpler and more transparent. | |
| Advice: | This is particularly useful where data which has been formatted in a PDF is copied and pasted to Excel. The N function can be applied to the values which may be formatted as - representing 0 values, making the usage of the pasted data very simple indeed. | |
| Examples: | Open / download sample workbook: N.xlsx | |