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

# #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
• 4
• 3
• 2
• +3
2 Solutions

Commented:
=IF(ISERROR(FORMULA),"ERROR","NOT ERROR")
0

Commented:
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

Data 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

Project 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

EngineerCommented:
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

Commented:
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

EngineerCommented:
Kris, why use index and then match if you can do it in a single vlookup?

Saqib
0

Commented:
Hi,

Experts say INDEX/MATCH works faster than VLOOKUP.

Kris
0

EngineerCommented:
Kris, I am not sure about that but I accept it.

Saqib
0

EngineerCommented:
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

Commented:
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

Commented:
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

Project 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.

## Featured Post

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