Oftentimes you have to move your Microsoft Excel workbook or file to another spreadsheet program that runs on a different operating system. In such cases, you might face some compatibility issues. Microsoft Excel has a function named N function which will help you avoid such compatibility issues. The N function in Excel will convert an input value to a number. 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 book to exercise the task while you are reading this article.
Introduction to the 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.
▶ Argument Explanation
|value||Required||The value you want to be converted.|
▶ Return Parameter
Returns a value converted to a number
5 Suitable Examples of N Function in Excel
N function Excel can convert Numbers, Dates, Text, Logical values, and Other types of characters to make the Excel file compatible with other types of Spreadsheet programs.
1. N Function Does Not Affect Any Numerical Values or Errors
If we write the following formula in cell D5
C5 = 100 = A Number
We will find that the N function returns the same number. 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 Using the N Function in Excel
Insert the following formula in cell D5
C5 = 05-01-2022 (5th January 2022) = A date in one of the built-in date formats available in Microsoft Excel
We will find that the N function converts the date to the serial number of the date.
Excel stores date as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1, and January 5, 2022, is serial number 44,566 because it is 44,566 days after January 1, 1900.
3. N function Converts Text to Zero
Write the formula below in cell D5
=N(C5) = Exceldemy = A Piece of Text
We will find that the N function converts the text to Zero (0).
4. N Function Converts Logical Values to 1 and 0
Enter the following formula in cell D5
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 Other Values to 0
Write down the following formula in cell D5
Here, C5 = #$% = Various Characters
We will find that the N function converts the string of characters to Zero (0).
Things to Remember
- It is not generally necessary to use the N function in a formula because Excel automatically converts values as necessary. This function is provided for compatibility with other spreadsheet programs.
- The N function removes text values. The T function removes numeric values.
In this article, we have learned to use the N function in Excel 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 do leave a comment below. Have a great day!!!