INTRODUCTION
N FUNCTION comes under the INFORMATION category in Excel.
N FUNCTION converts any given value to number.
This function is useful when we need to convert any given value into number before performing any operation on the values. Different types of values are converted to different type of numbers. The types are discussed later in the article.
PURPOSE OF N FUNCTION IN EXCEL
N FUNCTION converts any given value to number.
PREREQUISITES TO LEARN N FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Of course, Excel software.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: N FUNCTION
The Syntax for the function is
=N(VALUE)
VALUE VALUE TO BE CONVERTED TO NUMBER
| If value is or refers to | N returns |
|---|---|
| A number | That number |
| A date, in one of the built-in date formats available in Microsoft Excel | The serial number of that date |
| TRUE | 1 |
| FALSE | 0 |
| An error value, such as #DIV/0! | The error value |
| Anything else | 0 |
EXAMPLE:N FUNCTION IN EXCEL
DATA SAMPLE
Let us try to convert different types of values into number.
steps to use N function (example)
The example shown above demonstrates the usage of function N.
Column F has the sample text on which the function application will be done.
Column G has the text on which function is applied i.e. the output.
Column H;
shows the formula applied on the column F.
SAMPLE 1:
NUMBER 123 The number is itself is a number so no conversion in that. The output will remain the same as 123.
SAMPLE 2:
DATE 12-05-2021 As already discussed in many articles, the date in EXCEL is stored as a serial number since JAN 1 1900. So when we convert this date into a number by applying the function N, the output is shown as the day number since JAN 1 1900. i.e. 44328.
SAMPLE 3 & 4:
TRUE and FALSE are treated as 1 and 0 , as treated in binary, so applying the function N on the binary values TRUE and FALSE gives the output as 1 and 0.
SAMPLE 5:
Any error value will be returned as the type of error. e.g. error DIV/0 will be returned as the error DIV/0.
SAMPLE 6:
Any TEXT value , if operated by function N, the output will be 0, as the text is not a number.
USE :N FUNCTION IN EXCEL
N function converts all the values into numbers. (Only those formats which are basically a number such as binary, dates , number etc. otherwise the output will be zero).
The basic use of the function is when we need to consolidate the sheets from different programs for the compatibility purpose. For this, the different formats will be converted to the number first , compared and put back in the format we want.
