N Function
An Excel Text Function that returns the numerical counterpart for the supplied numerical values, text strings, boolean values, date and time, etc.
What is the N Function?
The N function is an Excel Text Function that returns the numerical counterpart for the supplied numerical values, text strings, boolean values, date and time, etc.
Some functions in Excel are so well known, such as the SUM, IF, and AVERAGE, that even a first-timer knows what particular role the process performs.
On the other hand, there are unusual functions that no one knows what they do, but they can be pretty handy if adequately utilized.
You must have gone through countless functions in our Excel articles repository, and if this is the first time you have landed on this page, we have good and bad news.
The bad news is that you might rarely use this function compared to all the other parts in the Excel library, whereas the good news is it is one of the most accessible functions to understand in the entire function’s library.
In this article, we will see the N function, how to use it, and a couple of examples in real-life scenarios.
Key Takeaways
- The N function is an Excel Text Function that converts non-numeric values to numbers.
- The N function converts non-numeric values to numbers. It returns 0 if the argument is not numeric, and it returns the numeric value if the argument is already a number.
- The syntax for the N function typically includes one argument representing the value to be converted to a number. The function returns the argument's numeric value.
- The N function does not typically encounter errors unless the specified argument is invalid or non-existent. In such cases, it may return a #VALUE! Error indicating the nature of the problem.
understanding the N function
The N is categorized as an Information function that returns the number for text strings, boolean values, date & time, etc.
The N can be interpreted as a ‘number,’ which is what the function does for the supplied value. Let’s see how the part reacts to different deals in Excel.
1. Numbers
Since traditional numbers are just ‘numbers,’ if we reference a cell containing numbers, the function will return the same result to the user. So, for example, 45 will return the result as 45 using the N function.
2. Text Strings and Booleans
When you reference a cell containing a text string or a boolean value inside the function, the circumstances change. We know that there are two boolean values, i.e., TRUE or FALSE, which correspond to the number 1 and 0, respectively.
When you reference a cell containing a boolean value in the function, the number will always be either 1 or 0, depending on whether the cell has the value as TRUE or FALSE.
As for the text strings, the numerical value associated with them is always equal to zero. So any text string, whether a hundred words long or a single character ‘g,’ will give the result as 0.
3. Date and Time values
Finally, we understand that even the date and time values are stored in Excel as serial and decimal numbers. So, for example, the dates begin from 1st Jan 1900, which is equal to serial number 1, while the time 12:00 PM is equal to 0.5 since it's half the time in the 24-hour clock.
When the date and time values are referenced in the function, we get the corresponding serial and decimal number.
The syntax for the function is:
=N(value)
where
- value - (required) the value whose numerical counterpart is to be returned.
Example of N function
Based on the information, the N function looks effortless for third-grade students.
All you need to do is begin with an equal sign and type in the letter N followed by the cell reference inside parentheses. This brings us to a critical section of the articles: the examples.
Suppose you have different data types in Excel, as illustrated below:
We will use the formula =N(B3) in cell C3 and drag it down to cell C13, which gives the result:
Interpretation
- When we had either positive or negative numbers, i.e., 21 and -18, respectively, the result was the same as the numbers in the formula.
- All the unique characters and text strings ‘Cryptocurrency’ return the result as 0.
- When you reference an empty cell, the result still returns as 0.
- Date and time values will return serial numbers and decimal numbers, respectively.
- All the error values will return errors as a result.
- The boolean values TRUE and FALSE return 1 and 0, respectively.
Practical Example of the n function
We are always curious about the serial number or decimal number for the date and time value. However, now we know we don’t need to use the Ctrl + ~ key to check those values. Instead, we can use the N function to do the magic.
That is a simple application for the function. A bit more complicated would be counting cells with characters greater than ‘n.’
Suppose you have the data, as illustrated below:
We have text strings in column C, and the length of the text strings that we have calculated using the LEN function.
The value in cell F3, i.e., 14, indicates that we are looking for all the text strings with a length greater than 14.
To get the count of all text strings with a length greater than 14, we will use the SUMPRODUCT, LEN, and N functions.
The formula will be =SUMPRODUCT(N(LEN(B3:B12)>F3)) in cell F4, which gives the result:
Out of all the given text strings in column B, there is just one text string with characters more remarkable than 14.
Note
Column C is included to help understand the length of different text strings and isn’t completely necessary.
T vs. N function
If ‘N’ stands for numbers, you would have probably guessed what ‘T’ stands for. The T function is categorized as a Text function that returns the text strings for a given value.
The function works oppositely to that of the N function. The T function will return a text string for the text values. However, the process will return an empty cell for all the other values.
This includes date and time, boolean, and even numerical values. Since all these values correspond to numbers and the function falls under the ‘text’ category, it's not difficult to imagine why it returns empty cells.
The syntax for the function is:
=T(value)
where
- value - (required) the value whose text counterpart is to be returned
Suppose you have the data as illustrated below:
We will use the formula =T(B3) in cell C3 and drag it down till cell C13, which gives the result:
Interpretation:
- As stated earlier, the function will return an empty cell when you have numerical values, date, time, or even boolean values.
- There is no significant effect on the error values. We get the same error as a result in column C.
- Finally, the text strings ‘Apple Inc’ and special characters also remain unaffected by the function and can return the text values using the formula.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?