Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

Fredledingue's Avatar
Fredledingue Fredledingue is offline
Member with 378 posts.
THREAD STARTER
 
Join Date: Aug 2003
Location: Klaipéda Lithuania
18-Oct-2003, 01:36 PM #1
How to change #N/A to 0 in Excell?
Is there a way to display zero (or nothing) when datas are missing for a formula? Instaed of #N/A in excell?
XL Guru's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
18-Oct-2003, 01:56 PM #2
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's Avatar
maxflia10 maxflia10 is offline
Member with 331 posts.
 
Join Date: Feb 2003
20-Oct-2003, 06:37 PM #3
Re: How to change #N/A to 0 in Excell?
Quote:
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(LookupVal ue,LookupTable,ColIdx,0),0)

or

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

5] Reject using either senseless

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

or inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupT able,ColIdx,0),0)
XL Guru's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
20-Oct-2003, 07:18 PM #4
>> 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's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
20-Oct-2003, 07:33 PM #5
>> 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's Avatar
maxflia10 maxflia10 is offline
Member with 331 posts.
 
Join Date: Feb 2003
20-Oct-2003, 07:35 PM #6
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's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
20-Oct-2003, 07:56 PM #7
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's Avatar
RandyG   (Randy) RandyG is offline RandyG has a Profile Picture
Member with 7,760 posts.
 
Join Date: Jun 2000
Location: American living in Dublin, Ire
Experience: Advanced
21-Oct-2003, 05:04 AM #8
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's Avatar
CastleHeart CastleHeart is offline
Senior Member with 743 posts.
 
Join Date: May 2002
Location: Coast of North Carolina
21-Oct-2003, 08:28 AM #9
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

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑