The N function converts non-number values to a number, dates to serial numbers, TRUE/FALSE to 1/0 respectively, and everything else to 0.
Syntax
= N([value])value = the value or input that is to be converted. Can be a cell reference or a hardcoded value.
Explanation
This function is categorized as a "Text Function", and its primary purpose is to return a specific number for a given value. The values returned by the function follow strict rules based on what is input into the function.
*All input values will follow the rules set in the table above.
Examples
1. How to Convert TRUE and FALSE to 1 and 0
This may be the most useful application of the N function. When dealing with a formula that returns an array of TRUE and FALSE values, this function can convert that into a series of 1's and 0's that can be summed and multiplied.
There are easier ways to accomplish this (the COUNTIF function), but as an example, let's say you wanted to count the amount of times a specific result appeared in a list.
= SUM(N( [range] = "Plum"))Here, this formula is creating a True/False array (shown in column C) based on whether or not the cell contains "Plum", and then converting that TRUE/FALSE array into 1s and 0s which is then summed up to get a total count of occurrences.
Formula in C3 = B3:B7 = "Plum" | Formula in D3 = N(B3:B7= "Plum")2. How to Convert Dates to Serial Numbers
If Excel can read a cell as a date, no matter what format it may be in, it can convert it to a serial number using the N function.
Excel stores dates as sequential serial numbers, using January 1st 1900 as the very first serial number "1". Excel then counts up from there to get the serial number for current date being used. Sometimes it is necessary to convert to serial numbers for integration with other programs or applications. The N function offers very easy flexibility for this if you are unable to change the date format of the cell itself.