# How to change #N/A to 0 in Excell?

Discussion in 'Business Applications' started by Fredledingue, Oct 18, 2003.

Not open for further replies.

Joined:
Aug 22, 2003
Messages:
378
Is there a way to display zero (or nothing) when datas are missing for a formula? Instaed of #N/A in excell?

2. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Example -

=IF(ISERROR(VLOOKUP(A1,\$D\$1:\$E\$2,2,FALSE)),"",VLOOKUP(A1,\$D\$1:\$E\$2,2,FALSE))

If the formula will result in an error, return "" -- ie: null.

Otherwise, just do it.

HTH,
Andy

3. ### maxflia10

Joined:
Feb 24, 2003
Messages:
331

A couple of options in order of efficiency and beauty...

In B1 enter:

=VLOOKUP(LookupValue,LookupTable,ColIdx,0)

In A1 enter:

=IF(ISNA(B1),0,B1)

=IF(ISNA(SETV(VLOOKUP(LookupValue,LookupTable,ColIdx,0))),0,GETV())

which is as efficient as Option 1.

3] If Option 2 is not feasible, add the following code to your workbook as a module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

and use:

=IF(ISNA(V(VLOOKUP(LookupValue,LookupTable,ColIdx,0))),0,V())

4] If efficiency is not important to you, use either

=IF(ISNUMBER(MATCH(LookupValue,INDEX(LookupTable,0,1),0)),VLOOKUP(LookupValue,LookupTable,ColIdx,0),0)

or

=IF(ISNA(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,LookupTable,ColIdx,0))

5] Reject using either senseless

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue,LookupTable,ColIdx,0))

or inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupTable,ColIdx,0),0)

4. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702

Who?

Where from?

>> 5] Reject using either senseless ...

Why?

Rgds,
Andy

5. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702

Rather than the extreme "use an entire other add-in for the sake of proofing one function" (more add-ins = more power-up time), you could also try conditional formatting.

Formula Is
=ISNA(range)

, Format -- Font -- Colour = white -- OK -- OK.

Rgds,
Andy

6. ### maxflia10

Joined:
Feb 24, 2003
Messages:
331
The problem with using ISERROR is that it doubles the cost of the formula, in that the VLOOKUP is executed twice.

If the first Vlookup errors out, we get 0. The Vlookup bit is then executed once.

If the first Vlookup succeeds, the condition of IF becomes of FALSE and necessarily the second Vlookup executed. Thus: Vlookup is executed twice.

http://longre.free.fr/english/index.html

Andy,

I meant no disrespect to you as you are a valuable contributor to this board. Just expressing a different view.

7. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Don't get me wrong, if you meant Aladin Akyurek, I'm familiar with *his* MS groups contributions ; he could eat me for breakfast.

I recognise that IF(ISERROR ... wastes to a degree. But to quote yourself "if efficiency is not important to you ...", on top of which I feel that it's easier to follow in some respects, ie: clearer for us non-rocket scientists.

With a UDF you're gonna get the macro nag screen.

Having considered your comments, I think my personal choice would be Conditional Formatting.

Rgds,
Andy

8. ### RandyG

Joined:
Jun 26, 2000
Messages:
7,762
Uhm, maybe I'm just being a little silly here, but could Fredledingue provide an example of the code he is using? I have used a nested If to display a blank instead of the #N/A in some formulas, but it will only work depending on what is trying to be done.

9. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
Hello folks;

It seems to me, after having a cup of coffee, that Fredledingue's question concerning #n/a could mean a whole bunch of #n/a's...
and maybe a few #DIV/0s or something.

If that is the case, rather than spending time complicating formula after formula - if it was me (and the spreadsheet lends itself to it) - Andy's conditional formatting suggestion would be the swiftest and simplest cure.

But then that's just me, because I always fail to annotate and then six months later go back and figure out what the idiot who wrote this formula (Me) was trying to do.

- Castleheart

As Seen On