If result is zero, give blank

nancymk

Board Regular
Joined
Jul 24, 2008
Messages
106
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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!
 
Upvote 0
Conditional formatting: set the font colour of cells containing zero to the same as the background colour.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top