Excel N Function (6 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

1-Excel N 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)

2-Syntax of N function

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.

3-the N function returns the same number

  • N function does not also affect the error and returns the same error code every time.

4-the N function returns the same error code


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.

5-Converting date to serial number

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).

6-The N function converts the text to Zero


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).

7-Converting Logical Values to 1 and 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).

8-Converting the string of characters to Zero


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)

9-Applying the SUM function

  • 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.

10-Selection of Data Validation feature

  • 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.

11-Use N function in the data Validation Source 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.

12-Applying Error Alert tab to show error message

  • 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.

13-Checking if the N function works or not

  • By inserting the numeric value, we will get the total sales in D12 using the SUM function.

14-Final output of 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!!!

<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo