Search Search for: Business ApplicationsAll Forums

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

 Fredledingue
Member with 378 posts.

Join Date: Aug 2003
Location: Klaipéda Lithuania
18-Oct-2003, 12: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?
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, 12: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
Member with 331 posts.

Join Date: Feb 2003
20-Oct-2003, 05: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?

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(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)
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, 06:18 PM #4

Who?

Where from?

>> 5] Reject using either senseless ...

Why?

Rgds,
Andy
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, 06:33 PM #5

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
Member with 331 posts.

Join Date: Feb 2003
20-Oct-2003, 06: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.

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.
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, 06: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   (Randy)
Member with 7,760 posts.

Join Date: Jun 2000
Location: American living in Dublin, Ire
21-Oct-2003, 04: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
Senior Member with 743 posts.

Join Date: May 2002
Location: Coast of North Carolina
21-Oct-2003, 07: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
 techguy.org/172874
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

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)