ASM Arman
The Excel N function is used to check whether a value is a number or not. It returns a Boolean value of either “TRUE” or “FALSE” based on the evaluation of the input.
The N function is particularly useful when working with data that may contain different types of values, such as numbers, text, or errors. It allows you to perform calculations or make logical comparisons based on the numerical nature of the data.
In this article, we will explain how to use the N function in Excel to make our Excel file compatible with other worksheet programs. Here’s an overview of the function.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
What Is Excel N Function?
▶ Function Objective
N function in Excel is a text function that will return a number when we provide a value. The function can be used to convert logical or Boolean values TRUE or FALSE into 1 and 0 respectively. It is a function with limited use and is primarily used to ensure compatibility with other spreadsheet programs.
▶ Syntax
=N(value)
▶ Argument Explanation
| Arguments | Required/Optional | Explanation |
|---|---|---|
| value | Required | The value you want to be converted. |
▶ Return Parameter
Returns a value converted to a number
| Input Type | Input Value | Output | N Function Returns |
|---|---|---|---|
| Any Number | 50 | 50 | Same Number |
| Recognized Date | Monday, January 3, 2022 | 44564 | N Function Converts Date into Serial Number Format |
| Text | Exceldemy | 0 | Number |
| Logical Value | TRUE | 1 | Number |
| Logical Value | FALSE | 0 | Number |
| Error | #NAME? | #NAME? | Same Error Code |
| Special Characters | #$% | 0 | Number |
Excel N Function: 6 Practical Examples
We will use an Excel N function to convert Numbers, Dates, Text, Logical values, and special characters. That’s why our Excel file is compatible with other types of Spreadsheet programs.
1. N Function Does Not Affect Any Numerical Values or Errors
In this example, we will demonstrate that the N function has no impact on numerical values or errors. Let’s follow the instructions below to learn!
- Insert the following formula in cell D5 and press Enter.
=N(C5)- Hence, AutoFill the formula to the rest of the cells. We will find that the N function returns the same number. The N function does not affect the numerical values and always returns the same number.
- N function does not also affect the error and returns the same error code every time.
2. Convert a Date into a Number Using N Function in Excel
Now, we will convert a Date into a Number format using the N function.
- Write down the following formula in cell D5, and hit Enter.
=N(C5)Here,
C5 = 05-01-2022 (5th January 2022)
- Now, use the AutoFill feature to copy the formula to the rest of the cells in column C. We will find that the N function converts the date to the serial number of the date.
Notes:
In Excel, dates are stored as sequential serial numbers to enable calculations. The default starting point is January 1, 1900, assigned the serial number 1. For instance, January 5, 2022, is represented by the serial number 44,566 since it is 44,566 days after January 1, 1900.
3. Apply N function to Convert Text to Zero
If a cell contains text, the N function will return Zero (0). Type the formula below in cell D5 and use the AutoFill handle for the rest of the cells in column C.
=N(C5)Here,
Cell C5 contains Exceldemy text.
We will find that the N function converts the text to Zero (0).
4. N Function Converts Logical Values to 1 or 0 (Numbers)
In this example, we will learn how the N function converts the logical value to the Binary logical value (1 or 0). Enter the following formula in cell D5 and AutoFill the formula.
=N(C5)Here, C5 = TRUE = Logical Value
We will find that the N function converts TRUE to One (1) and FALSE to Zero (0).
5. N Function Converts Special Characters to 0
Sometimes our worksheet contains special characters. The N function converts those characters into Zero (0). Write down the following formula in cell D5.
=N(C5)Here, C5 = #$% = Various Special Characters
We will find that the N function converts the string of characters to Zero (0).
6. Check a Value with Data Validation Feature With N Function
Last but not least, we will apply the SUM function to sum up the total sales using the Data Validation drop-down list and the N function. Let’s follow the instructions below to learn!
- Select cell D12 and insert the SUM function.
=SUM(D5:D11)
- Now, we will use the Data Validation feature to apply the N Select cells where you want to apply the N function.
- Go to the Data tab >> Select the Data Validation feature from the Data Tools group.
- As a result, a Data Validation dialog box pops up. From that dialog box, choose Settings tab >> Choose Custom under the Allow drop-down list >> Write down =N(D5) in the Formula typing box.
- Now, select the Error Alert tab >> type Error Message in the Title box >> Write down The value is not Numeric in the Error Message typing box >> Press OK.
- After that, we will check whether the N function works or not. Type TV in cell D6 and press Enter.
- As a result, A warning message shows “The value is not Numeric” because the value of cell D6 is a text value.
- By inserting the numeric value, we will get the total sales in D12 using the SUM function.
Things to Remember
- The N function removes text values. The T function removes numeric values.
- The N function treats error values, such as #VALUE!, #DIV/0!, or #N/A, as non-numeric and returns “FALSE” for them.
- The N function works with various types of inputs, including numbers, text, logical values, and errors. It tries to evaluate the input and determine if it represents a number.
- If a cell contains a number formatted as text, the N function considers it as a non-numeric value and returns “FALSE.” To convert text numbers to actual numbers, you can use the VALUE function.
Frequently Asked Questions
1. Does the N function consider numbers formatted as text as numbers?
Answer: No, the N function treats numbers formatted as text as non-numbers and returns “FALSE” for them. To convert numbers formatted as text into actual numbers, you can use the VALUE function.
2. Can I use the N function for error handling?
Answer: Yes, the N function can be used in error handling. By wrapping a formula with the N function, you can check if the result is a number or an error. This can help you handle errors appropriately in your calculations or display custom messages.
3. Can the N function check if a value is a specific type of number?
Answer: No, the N function only checks whether a value is a number or not. It does not distinguish between different types of numbers, such as whole numbers or fractions.
Conclusion
In this article, we have learned to use the Excel N function to return a number when a value is provided. The N function in Excel helps the Excel file to be compatible with other spreadsheet programs. If you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!
