In the previous blog, we did the usage of the T Function. In this blog, we would cover the entire work of the N Function in Excel. N Function is a type of Information function in Excel.
So let’s jump to work!
When to Use N Function in Excel?
The N Function coverts any non-numerical value into a number. The non-numerical value can either be a text string, date or a logical value ( TRUE or FALSE ).
An error when passed as the N Function argument, returns an error.
Basically, the numerical values and erroneous values when passed as the N Function argument remains unaffected.
Syntax and Arguments
The following points would explain to you, the required function argument for the N Function in Excel.
- value – This can be a hard-coded value, a cell reference, a range of cells, a named range or an array of values returned by some other formula into the N value input.
Important Points to Remember
The following points about the N function must be kept in mind, before implementing the formula.
- The N Function covers Logical TRUE and FALSE into 1 and 0. This is why we can replace it with the double minus operator ( — ).
- The function can operate on a single value as well as on a range of values.
- A date in Excel, when passed as N function input, returns the numerical value of the date.
- The text string, when passed as N Function input, returns a 0.
Examples to Learn the N Function
In the following section of the blog, we would run some practical examples for learning the N Function in Excel.
Example 1 – Fundamental Example for the N Function
Let us suppose we have the following range of values.
Use the following formula in cell B2.
Select the range B2:B9 and press the Ctrl D key to copy the formula down the range B2:B9.
As a result, the formula returns the numerical values in the range B2:B9.
Explanation – We have passed the cell A2 as the N Function’s input argument value. Since the numerical values remain unaffected, the cell A2 and A9 remained unaffected in resultant N formula result in cell B2 and B9.
- Logical values in cell A3 and A4 returned one and zero respectively.
- The text string “ExcelUnlocked” in cell A5 returns a zero.
- We have got the numerical value of the date in cell A6 as 37539.
- The cell A7 and A8 resulted into erroneous value as the errors as input retuns error in return.
Example 2 – Replacing Double Minus operator with N Function
The Double Minus operator is used to convert a range of logical value like TRUE and FALSE into 1 and 0. This same functionality is provided by the N Function.
In this example, let us say we have the orders of different colored clothes.
By using the N Function with SUMPRODUCT Formula, we want to get the total of sales of Red Colored Cloth.
Use the following formula to get the total sales based on the criteria that the Cloth Color must be Red.
As a result, this formula gives the total sales for Red color cloth as 36000.
Explanation – We have taken a condition that color cloth must be Red. So the formula has the condition that range A2:A12 = “Red” . This condition will compare each cell in range A2:A12 with “Red” and returns TRUE for cells that contain Red and FALSE otherwise.
Condition results would be:-
This boolean array is passed to the N Function, which converts TRUE to 1 and FALSE to 0.
The SUMPRODUCT Formula is suppleied with N formula result and the range B2:B12 containg the sales. It performuls the product of boolean array and sales.
At last the product results of boolean array and range B2:B12 are added to give the result.
This brings us to the end of blog.
Thank you for reading.