The Excel N function lets you convert a given value into a valid numeric value. It is mainly used to convert logical values as follows:
- TRUE into 1
- FALSE into 0
It is mainly used to ensure compatibility with other programs.
For example, suppose that you conducted an employee survey to collect information about the benefits of working from home. The first question in this survey form is “Do you work remotely at least once a week?”. Refer to the following image.
The answer to this question can be either TRUE or FALSE. Your task is to convert these responses (logical values) into numbers.
Our formula would be as follows:
=N(B2)Where cell B2 holds the first response or logical value to be converted into a number.
The results are displayed in the following GIF.
I am using the Conditional Formatting feature in Excel to highlight the cell here. It helps us visually analyze both responses.
Syntax
The N is a simple function with the following syntax.
=N(value)Where the value argument must be replaced with the desired value to be converted to a valid number.
Here is a quick list of different input values:
| Input Values | Output |
|---|---|
| Any Number | Same number |
| A valid date | Five digit serial number representing the date in Excel. |
| Logical values | 1 for TRUE0 (zero) for FALSE |
| Errors | Same error code for all types of errors in Excel. |
| Other values | 0 (zero) |
Important Notes:
- The function is not case-sensitive.
- Numbers are unchanged. They are returned as is.
- If you specify the cell reference that is empty or blank, the function returns an empty string.
How to use the N Function in Excel
Download the example spreadsheet used to demonstrate the N function in the upcoming section. Practice is the key to success!
Example 1: N Function Basics
Suppose that you are an HR Manager responsible for organizing and maintaining employee data in Excel. While exporting this data to a newly purchased employee management tool, you are getting error due to dates in the file. Refer to the following image.
As you can see in the above image, the dates are entered in one of the valid date formats in Excel. Now, for the sake of export, we need to convert these dates into valid serial numbers.
We can use the N function in Excel here.
The steps are as follows:
- Select the cell E2
- Type =N
- Choose the first option from the popup
- Specify the cell reference D2
- Complete the parentheses using )
- Hit the Enter key
Our formula would be as follows:
=N(D2)Where cell D2 contains the first date to be converted to a valid serial number.
The results are displayed in the following image.
Takeaway
N is one of the rarely used functions in Excel. It helps ensure compatibility with the legacy spreadsheet tools and avoids errors in calculations.
It is one of the two functions in Excel that contains only one character.
I hope this article taught you all the bells and whistles of the N function. Please comment below if you are stuck or encounter any particular error while using it. I will answer your questions as soon as possible.
Additional Resources:
- Learn All Excel Information Functions (With Examples)
- T Function in Excel
- IF Function in Excel
- Types of Errors in Excel
- Guide to Conditional Formatting in Excel
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.