Getting rid of #N/A

[neospec]

I have a formula, but when it is unable to retrieve information it gives a #N/A error.

Is there anyway to change this error to $0.00 or to get rid of it completely?

 

[NateO]

http://www.google.com/search?q=site:MrExcel.com+#N/A+IsNA

 

[sand112281]

use an if statement. =if(isnumber(a1)=true,a1,0)

put your formula as a1 or some equivalent.

 

[Jonmo1]

=IF(ISNA(VLOOKUP(A1,B1:C20,2,FALSE))=TRUE,"",VLOOKUP(A1,B1:C20,2,FALSE))

Modify the Vlookup functions to suit your needs. This will result in blank if result of vlookup is #N/A. You can also put $0.00 inside the Quotation marks if you want it to display $0.00 when result is #N/A.

 

[neospec]

My formula is currently

=INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2)

So, how would I implement

=IF(ISNA(VLOOKUP(A1,B1:C20,2,FALSE))=TRUE,"",VLOOKUP(A1,B1:C20,2,FALSE))

 

[sand112281]

you dont need the vlookups

assuming that the result of the formula is a number.

=if(isna(INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))=true,0,INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))

you could replace the 0 with "" depending on what you want there.

 

[Jonmo1]

I found 2 ways. I'm pretty new here, I'm sure somebody else will find a better way.

Method 1, (and the easier way if you ask me)

Add a collumn next to the collumn with your formula and use this.

Collumn A contains your formula

Collumn B
=IF(ISNA(A1)=TRUE,"$0.00",A1)

Then if you want, you can hide the collumn that contains the main formula.

Method 2 seems far too extreme to me (looks like sand112281 already did this one)

Applying the isna function to your existing formula would make the formula extremely long. But here's how you would do it. Just insert your formula (without the equal sign) in both places where I put a vlookup formula. Something like this :

=IF(ISNA(Insert your formula here)=TRUE,"$0.00",Insert your formula here)

=IF(ISNA(INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))=TRUE,"$0.00",INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))

 

[NBVC]

I have a formula, but when it is unable to retrieve information it gives a #N/A error.

Is there anyway to change this error to $0.00 or to get rid of it completely?

Try:

=IF(ISNUMBER(MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2),INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2),0)

and format the cell accordingly

 

[neospec]

Ok, tried the last one, and got some whacky incorrect values.

 

[NBVC]

Should've worked. But you could take out the +2 from the Match condition after the IF()...

=IF(ISNUMBER(MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)),INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH($B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2),0)