How to get rid of #N/A in a box?

[lala121]

So basically I have a quotation system for an assignment and built everything. But when nothing is selected the box which have formulas i.e. if statement, calculation and Index Match say #N/A. How do i get rid of it so basically it displays nothing in there.

 

[Andrew Poulsom]

What version of Excel do you have? Maybe you can use IFERROR (Excel 2007 and above).

 

[BengalMagic]

wrap an =if(iserror( around your formula

 

[VoG]

Try like this

=IF(ISNA(your formula),"",your formula)

 

[lala121]

Hi thanks for quick reply!! i tried that but it saying something about brackers and stuff. here are the 3 formulas which i need them to be done.

=INDEX(Prices,MATCH(Quotations!R13,Prices!B4:B29,0),MATCH(Quotations!R16,Prices!C3:D3,0))
=R19*20%
=IF(L13="Yes", "£50")+(L22*R19)+R22

 

[Aladin Akyurek]

Hi thanks for quick reply!! i tried that but it saying something about brackers and stuff. here are the 3 formulas which i need them to be done.

=INDEX(Prices,MATCH(Quotations!R13,Prices!B4:B29,0),MATCH(Quotations!R16,Prices!C3:D3,0))
=R19*20%
=IF(L13="Yes", "£50")+(L22*R19)+R22

If on a post-2003 system...

=IFERROR(INDEX(Prices,MATCH(Quotations!R13,Prices!B4:B29,0),
MATCH(Quotations!R16,Prices!C3:D3,0)),0)

Otherwise:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
INDEX(Prices,MATCH(Quotations!R13,Prices!B4:B29,0),
MATCH(Quotations!R16,Prices!C3:D3,0))))

Do we really need error rapping with the other two? If yes and a post-2003 system:

=IFERROR(R19*20%,0)
=IFERROR(IF(L13="Yes", "£50")+(L22*R19)+R22,0)

 

[lala121]

Omg thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[Aladin Akyurek]

Omg thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You are welcome.