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.