• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

#N/A - remove it

Experts, how could I remvove the #N/A that I get in the result?  Is it IFERROR?  

=IF(AND($F154>$I$19,$C154<$J$19),IF($G154="USD",$H154,(IF($G154=$H$4,$H154*$I$4,(IF($G154=$H$5,$H154*$I$5,(IF($G154=$H$6,$H154*$I$6,(IF($G154=$H$7,$H154*$I$7,(IF($G154=$H$8,$H154*$I$8,(IF($G154=$H$9,$H154*$I$9,(IF($G154=$H$10,$H154*$I$10,(IF($G154=$H$11,$H154*$I$11,(IF($G154=$H$12,$H154*$I$12,(IF($G154=$H$13,$H154*$I$13,(IF($G154=$H$14,$H154*$I$14,(IF($G154=$H$15,$H154*$I$15,(IF($G154=$H$16,$H154*$I$16,(IF($G154=$H$17,$H154*$I$17,(IF($G154=$H$18,$H154*$I$18,0))))))))))))))))))))))))))))))),0)
0
pdvsa
Asked:
pdvsa
  • 4
  • 3
  • 2
  • +3
2 Solutions
 
jimyXCommented:
=IF(ISERROR(FORMULA),"ERROR","NOT ERROR")
0
 
Patrick MatthewsCommented:
pdvsa,

In Excel 2007 or later you could use IFERROR to return an alternate value:

=IFERROR(IF(AND($F154>$I$19,$C154<$J$19),IF($G154="USD",$H154,(IF($G154=$H$4,$H154*$I$4,(IF($G154=$H$5,$H154*$I$5,(IF($G154=$H$6,$H154*$I$6,(IF($G154=$H$7,$H154*$I$7,(IF($G154=$H$8,$H154*$I$8,(IF($G154=$H$9,$H154*$I$9,(IF($G154=$H$10,$H154*$I$10,(IF($G154=$H$11,$H154*$I$11,(IF($G154=$H$12,$H154*$I$12,(IF($G154=$H$13,$H154*$I$13,(IF($G154=$H$14,$H154*$I$14,(IF($G154=$H$15,$H154*$I$15,(IF($G154=$H$16,$H154*$I$16,(IF($G154=$H$17,$H154*$I$17,(IF($G154=$H$18,$H154*$I$18,0))))))))))))))))))))))))))))))),0),"N/A")

That said, you should first understand what the cause of that error is, and determine whether or not simply suppressing the error is the right move for your design.

I would also try to avoid so many nested IF expressions.  You may understand what this formula is doing now, but if you have to edit this a year from now, good luck unraveling the logic--I know I would struggle to :)

Patrick
0
 
AnilData ManagerCommented:
Other less elegant solutions (depends on the point of view) is to hide the N/A using conditional formatting to set the font color the same as the background.
IFERROR is Excel 2007 onwards only.

A>
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
pdvsaProject financeAuthor Commented:
Yes those nested IF's are a tough one.  I inherited this Excel spreadsheet and am trying to udnerstand it.  I am using Excel 07 and think IFERROR is best.  
0
 
Saqib Husain, SyedEngineerCommented:
Try to use this formula instead of the monster formula

=IF(AND($F154>$I$19,$C154<$J$19),IF($G154="USD",$H154,$H154*VLOOKUP($G154,$H$4:$I$18,2,0)),0)

and if it works you can apply the #NA check (Not tested)

=iferror(IF(AND($F154>$I$19,$C154<$J$19),IF($G154="USD",$H154,$H154*VLOOKUP($G154,$H$4:$I$18,2,0)),0),"")


Saqib
0
 
krishnakrkcCommented:
Hi,

=IFERROR(IF(AND($F154>$I$19,$C154<$J$19),IF($G154="USD",$H154,INDEX(I4:I18,MATCH(G154,H4:H18,0))*H154),0),"")

Kris
0
 
Saqib Husain, SyedEngineerCommented:
Kris, why use index and then match if you can do it in a single vlookup?

Saqib
0
 
krishnakrkcCommented:
Hi,

Experts say INDEX/MATCH works faster than VLOOKUP.

Kris
0
 
Saqib Husain, SyedEngineerCommented:
Kris, I am not sure about that but I accept it.

Saqib
0
 
Saqib Husain, SyedEngineerCommented:
Kris, I have made a quick search and what I have understood is that as far as speed is concerned there is almost no difference between the two.

If, however, there are a number of such lookups to be performed on a given number then the result of the match should be stored and used in seperate index functions. This will give a significant improvement in performance.

I am open to further comments.

Saqib
0
 
Patrick MatthewsCommented:
INDEX/MATCH can perform faster than VLOOKUP, especially for exact matches.  It also allows the reference column to be other than the leftmost in the lookup table.

That said, it is a heck of a lot easier to tell people how to use VLOOKUP than how to use INDEX/MATCH :)
0
 
krishnakrkcCommented:
Hi Saqib,

I google it and found this:

http://www.decisionmodels.com/optspeede.htm

My statement is not correct as far as speed is concern, although they recommend INDEX/MATCH.

Kris
0
 
pdvsaProject financeAuthor Commented:
thank you...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now