Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Michigan, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero and Blanks (2000)

    Hello everyone. When evaluating an IF function, even though the cell is "blank" it sometimes returns a zero value. Is there anyway/function to force a cell to be blank? That is, if the cell contains a zero, can I use some type of function to force the cell's value to result in blank (rather than a zero). I've tried setting the IF function to "" but when testing for ISBLANK the result is FALSE and returns a zero. Any insight, as always, is greatly appreciated...

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero and Blanks (2000)

    Sorry, but XL does not have a NULL function.

    The result of a formula is always something other than nothing <g>.
    Using "" is the best you can do.
    If you need data for charting and want the "empty" cells not to be plotted, use NA() in stead of "".

    ***Edited to add:***

    Even if one refers to an empty cell in a formula, XL evaluates that cell to a zero value.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero and Blanks (2000)

    Are you trying to blank the real 0 values?
    Microsoft MVP - Excel

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Zero and Blanks (2000)

    Steve,

    If all you want to do is to not display zero values, you could use Tools|Options|View and uncheck the "Zero values" box.

    In an IF formula, you could use something like:
    =IF(A1="","", *then the alternate result* )
    This will return nothing if the cell is empty, but treats a true zero (or anything else) differently. Similarly,
    =IF(A1=0,"", *then the alternate result* )
    will return nothing if the cell is empty or has a true zero, but treats anything else differently.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero and Blanks (2000)

    I'm not entirely clear what you want to do, but
    <pre>=IF(A1=0,"Zero!","Not zero")</pre>

    will return "Zero!" if A1 contains a 0 OR if A1 is blank.

    Does that help?

    Ian.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •