Getting rid of zero's in cells that have formulas
Getting rid of zero's in cells that have formulas
(OP)
I have a cell that multiplies one cell by another and displays the total. The trouble is that if nothing is in the other cells it just displays a '0'.
Is there a way of setting it to not display a '0' and just remain blank ?
Thank you for your help
John
Is there a way of setting it to not display a '0' and just remain blank ?
Thank you for your help
John





RE: Getting rid of zero's in cells that have formulas
TTFN
RE: Getting rid of zero's in cells that have formulas
In the cell you are having trouble with, right click then click on "format cells..." and choose the "Number" Tab. What you want to do is add two semicolons to the end of whatever number format you have. If you have no format, click on "custom" and enter something like this:
0.00;;
That way, when you get a return value of 0, nothing is displayed.
Hope this helps!
jproj
RE: Getting rid of zero's in cells that have formulas
A better a cleaner way it to do the whole thing in VBA, where your function or formula is copied down only when extra data is given. i-e you get a Y result only is an X is supplied.
RE: Getting rid of zero's in cells that have formulas
That's the one !!
Worked a treat, thanks for your replies.
Cheers
JT
RE: Getting rid of zero's in cells that have formulas
go to Tools -> Options -> Display
uncheck the box for "zero values".
RE: Getting rid of zero's in cells that have formulas
Thanks for that one. Didn't know about that. In this case there are other values that I want to show zero, but I'll certainly bear it in mind for the future.
Thanks for your help
JT
RE: Getting rid of zero's in cells that have formulas
However jproj's specific example of a custom format will also display as blank for negative numbers, which might not be what you want. This form of custom format consists of three specifications separated by two semi-colons. The first specification is used for positive numbers, the second for negative numbers, the third for zero numbers. So a more engineeringly-appropriate alternative to jproj's example might be
0.00;-0.00;
Avoid the use of IF() combined with "" as recommended in some of the other posts. I used to use this approach before I discovered the custom format approach, and I know from hard-earned experience that it can lead to subsequent complications when the cell concerned is itself used in later formulae. These problems stem from the fact that the zero contents of the cell can be seen as non-numeric in some contexts.
RE: Getting rid of zero's in cells that have formulas
This has worked out fine.
Thanks for your help.
Cheers
JT