Tuesday, January 29, 2013

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)


  1. Excel Everest l like this blog along with both blogs are really helpfull for me to learn excel.

    Excel Everest

  2. The Formula shown above was really helpful for me. Microsoft Excel Training Everest offers Microsoft ExcelTutorials: Our administrations are Microsoft Excel Training, Online Excel Training,Excel Courses, Excel Classes, Learn Excel.

  3. I wish more authors of this type of content would take the time you did to research and write so well. I am very impressed with your vision and insight.
    excel vba training

  4. Nice to read this read this article.... Thanks for sharing.....
    excel training

  5. Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
    online Python training
    python training in chennai

  6. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    Best Devops training in sholinganallur
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram

  7. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    angularjs Training in bangalore

    angularjs Training in bangalore

    angularjs Training in btm

    angularjs Training in electronic-city

    angularjs online Training

    angularjs Training in marathahalli

  8. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you.
    Keep update more information..

    Selenium training in bangalore
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training
    Selenium interview questions and answers

  9. Thanks for sharing article. The way you have stated everything above is quite awesome. Keep blogging like this. Thanks.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery


Share your comment here..