1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

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

Thread Status:
Not open for further replies.
Advertisement
  1. Fredledingue

    Fredledingue Thread Starter

    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

    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

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    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)
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> This is from Aladin

    Who?

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

    Where from?

    >> 5] Reject using either senseless ...

    Why?

    Rgds,
    Andy
     
  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> 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
     
  6. maxflia10

    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.

    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.
     
  7. XL Guru

    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

    RandyG

    Joined:
    Jun 26, 2000
    Messages:
    7,760
    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

    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 :)
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/172874