Getting rid of #N/A

neospec

New Member
Joined
Oct 19, 2006
Messages
23
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

sand112281

Board Regular
Joined
Aug 6, 2004
Messages
75
use an if statement. =if(isnumber(a1)=true,a1,0)

put your formula as a1 or some equivalent.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=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

New Member
Joined
Oct 19, 2006
Messages
23

ADVERTISEMENT

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

Board Regular
Joined
Aug 6, 2004
Messages
75
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,685
Members
410,697
Latest member
srishtijain0708
Top