×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

RE: Getting rid of zero's in cells that have formulas

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

RE: Getting rid of zero's in cells that have formulas

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

RE: Getting rid of zero's in cells that have formulas

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.

RE: Getting rid of zero's in cells that have formulas

(OP)
jproj,

That's the one !!

Worked a treat, thanks for your replies.

Cheers

JT

RE: Getting rid of zero's in cells that have formulas

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".

RE: Getting rid of zero's in cells that have formulas

(OP)
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

RE: Getting rid of zero's in cells that have formulas

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.

RE: Getting rid of zero's in cells that have formulas

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources