If result is zero, give blank

[nancymk]

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

[Bob Phillips]

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.

[nancymk]

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!

[Bob Phillips]

Did you see my last suggestion, about suppressing zeroes?

[Ruddles]

Conditional formatting: set the font colour of cells containing zero to the same as the background colour.

[Aladin Akyurek]

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.

[bosco_yip]

Or,

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

Regards

[nancymk]

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!

[Aladin Akyurek]

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.