# #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)
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.