IFNA Function - New logical function in Excel 2013

Finally MS Excel 2013, is available in the market. Along with the wonderful features like Flash Fill and Quick Analysis Tool there are lot of new features are enabled in Excel 2013.

Apart from the new features, Excel 2013 released new functions also. Here we are going to discuss about the new Logical Functions in Excel 2013 those are IFNA & XOR.

In this post we will concentrate on the function IFNA, which is only available from Excel version 2013 and above.

IFNA Function:

Syntax: =IFNA(value, value_if_na)

IFNA returns the value you specify if the formula returns #N/A error value, otherwise returns the result of the formula

The name actually suggesting the same description. In simple words, if the result is #N/A then the result is value_if_na, if not then the result is value.

We can have better understanding through the example.

Let say, I am using VLOOKUP function to look up the marks of the students from master marks list.

In the above example, for the third student Excel resulted error (#N/A) since Krishna is not there in the master list.

If we want to further use AVERAGE function to the resulted marks, even the excel results as #N/A since one or more of the inputs are errors.

So, IFNA formula is to replace the error (#N/A) with any of the desired value.

Look here, the same example by IFNA Function

If you observe above, all the results are same from the above example except for the third student. Here, IFNA function replaces the #N/A error with the value 0.

AVERAGE function also results the correct value, as there is no error in the range now.

In next post, I will explain how XOR function works.

(IFNA Formula in excel 2013, how IFNA formula works in excel 2013, what is IFNA formula, new IFNA formula in excel 2013)