Results 1 to 9 of 9

If result is zero, give blank

This is a discussion on If result is zero, give blank within the Excel Questions forums, part of the Question Forums category; Is there any formula that you can wrap around another formula, that if the result is zero to give you ...

  1. #1
    Board Regular
    Join Date
    Jul 2008
    Posts
    106

    Default If result is zero, give blank

    Is there any formula that you can wrap around another formula, that if the result is zero to give you a blank instead? Without having to write:

    if(formula=0,"",formula)

    I don't want to write the formula out twice in the cell. I just want it to null zero results.

    Thanks

  2. #2
    Banned
    Join Date
    Nov 2010
    Posts
    6

    Default Re: If result is zero, give blank

    If you have Excel 2007, you can use

    =IFERROR(formula,"")

    otherwise I cannot see that you have any choice.

    Of course, there is one other choice. If you are just referring to a result of zero, you could suppress zero display, Tools>Options>View, uncheck the checkbox.

  3. #3
    Board Regular
    Join Date
    Jul 2008
    Posts
    106

    Default Re: If result is zero, give blank

    Thanks for the help. Someone actually gave me that formula before, but this isn't a error. The result should be zero. Like a CSE with no result that shows zero, or a lookup to another sheet and the cell is blank, but it returns a zero.

    I will continue to do the formula the way I was, but maybe in a later version they will develop an IFZERO

    Thanks!

  4. #4
    Banned
    Join Date
    Nov 2010
    Posts
    6

    Default Re: If result is zero, give blank

    Did you see my last suggestion, about suppressing zeroes?

  5. #5
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,473

    Default Re: If result is zero, give blank

    Conditional formatting: set the font colour of cells containing zero to the same as the background colour.
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,817

    Default Re: If result is zero, give blank

    Quote Originally Posted by nancymk View Post
    Is there any formula that you can wrap around another formula, that if the result is zero to give you a blank instead? Without having to write:

    if(formula=0,"",formula)

    I don't want to write the formula out twice in the cell. I just want it to null zero results.

    Thanks
    If you have the morefunc add-in installed...

    =IF(SETV(FormulaExpression),GETV(),"")

    =IF(SETV(FormulaExpression)="","",GETV())

    If you can't install this add-in for some reason, add the following code to your workbook:

    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

    Nov replace both SETV and GETV with V in the foregoing formulas:

    =IF(V(FormulaExpression),V(),"")

    =IF(V(FormulaExpression)="","",V())

    If you want to trap error results (this is a different task)...

    Excel 2007 and later...

    =IFERROR(FormulaExpression,"")

    Earlier versions...

    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,FormulaExpression))

    for formula expressions which are supposed to return a number as result.
    If you don't want to see 0, try custom formatting the formula cell as, e.g.,

    [=0]"";General

    =LOOKUP(REPT("z",255),CHOOSE({1,2},"",FormulaExpression))

    for formula expressions which are supposed to return a text result.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default Re: If result is zero, give blank

    Or,

    =TEXT(formula,"0;-0;;")

    Regards

  8. #8
    Board Regular
    Join Date
    Jul 2008
    Posts
    106

    Default Re: If result is zero, give blank

    The replies went to my spam folder, so I just notice all the feedback!! Thanks for all the help. I like the text formula the best!

    =TEXT(formula,"0;-0;;")

    If you happen to see my reply, could you just let me know what that is saying? I would just like to know incase I need some sort of variation in the future.

    Thanks!

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,817

    Default Re: If result is zero, give blank

    Quote Originally Posted by nancymk View Post
    The replies went to my spam folder, so I just notice all the feedback!! Thanks for all the help. I like the text formula the best!

    =TEXT(formula,"0;-0;;")

    If you happen to see my reply, could you just let me know what that is saying? I would just like to know incase I need some sort of variation in the future.

    Thanks!
    If you need to further process the outcome, you have take into account the fact that the outcome is not a true number.
    Assuming too much and qualifying too much are two faces of the same problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com