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



## Fredledingue (Aug 22, 2003)

Is there a way to display zero (or nothing) when datas are missing for a formula? Instaed of #N/A in excell?


----------



## XL Guru (Aug 30, 2003)

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


----------



## maxflia10 (Feb 25, 2003)

> _Originally posted by Fredledingue:_
> *Is there a way to display zero (or nothing) when datas are missing for a formula? Instaed of #N/A in excell? *


This is from Aladin, interesting reading.

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

1] Use an additional cell:

In B1 enter:

=VLOOKUP(LookupValue,LookupTable,ColIdx,0)

In A1 enter:

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

2] Download & install the free morefunc.xll add-in & use:

=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)


----------



## XL Guru (Aug 30, 2003)

>> This is from Aladin

Who?

>> 2] Download & install the free morefunc.xll add-in ...

Where from?

>> 5] Reject using either senseless ...

Why?

Rgds,
Andy


----------



## XL Guru (Aug 30, 2003)

>> Download & install the free morefunc.xll add-in ...

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


----------



## maxflia10 (Feb 25, 2003)

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.

For Morefunc add-in

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.


----------



## XL Guru (Aug 30, 2003)

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


----------



## RandyG (Jun 26, 2000)

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.


----------



## CastleHeart (May 4, 2002)

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


----------

