Getting rid of zero's in cells that have formulas 2

[JohnnyT]

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

 

[IRstuff]

One simple solution is to wrap an IF function around the equation so that if the equation results in a non-zero value, the IF statement returns the value of the equation, otherwise, it returns double-quotes, which will result in a blank cell.

TTFN

 

[jproj]

JohnnyT:

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

 

[SErgini0]

I usually wrap it up with an If statement,
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.

 

[JohnnyT]

jproj,

That's the one !!

Worked a treat, thanks for your replies.

Cheers

JT

 

[anka]

You don't have to change the format for each individual cell. You can change it for the whole sheet at once.

go to Tools -> Options -> Display
uncheck the box for "zero values".

 

[JohnnyT]

anka

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

 

[Denial]

Best solution is the use of a custom format (as recommended by jproj - but see below), or the global solution (as recommended by anka).

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.

 

[JohnnyT]

Thanks for the input, its duly noted. I have settled for the 0.00;red -0.00;; approach.

This has worked out fine.

Thanks for your help.

Cheers

JT