Status
Not open for further replies.
1 - 9 of 9 Posts

#### Fredledingue

· Registered
Joined
·
404 Posts
Discussion Starter · ·
Is there a way to display zero (or nothing) when datas are missing for a formula? Instaed of #N/A in excell?

#### XL Guru

· Registered
Joined
·
2,708 Posts
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

· Registered
Joined
·
331 Posts
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?

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

· Registered
Joined
·
2,708 Posts
>> 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

· Registered
Joined
·
2,708 Posts

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

· Registered
Joined
·
331 Posts
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.

#### XL Guru

· Registered
Joined
·
2,708 Posts
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

· Registered
Joined
·
8,064 Posts
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

· Registered
Joined
·
743 Posts
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

1 - 9 of 9 Posts
Status
Not open for further replies.