Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Suppress zeros and errors (excel 97)

    Hi all,

    I hope someone can help me with a formula. Can I write a formula in a cell so that when the equation equals zero or a #VALUE! then it would leave the cell blank?

    Thanks

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suppress zeros and errors (excel 97)

    =IF(ISERROR(A3)," ",IF(A3=0," ",A3))

    Substitute your equation in each place where there is an A3. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress zeros and errors (excel 97)

    I believe it's better to track down why you get a #VALUE! error instead of to suppress it.
    If you produce lots of blanks ("") by means of formulas, you're bound to get #VALUE! errors. For example, if A1 contains a formula produced "" and B1 a number, =A1+B1 will end up in a #VALUE error.

    If returning a 0 is not a problem for further processing that you do, by all means return a 0. If you like not to see 0 in a cell, custom format that cell as:

    [=0]"";General
    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suppress zeros and errors (excel 97)

    Aladin has excellent advice. I wondered about the #VALUE!: it usually means deal with this now! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Location
    Franklin, Ohio, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress zeros and errors (excel 97)

    How would you format to blank a cell that contains - 0.00 ?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress zeros and errors (excel 97)

    I take it that you mean:

    0.00

    Activate the cell of interest.
    Activate Format|Cells.
    Choose Custom on the Number tab.
    Enter in the box for Type:

    [=0]"";General

    Aladin
    Microsoft MVP - Excel

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Suppress zeros and errors (excel 97)

    You can supress the display of just about anything you want by using Conditional Formatting to set a white font on a white background for the values you do not want to display.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suppress zeros and errors (excel 97)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > How would you format to blank a cell that contains - 0.00 ?
    <img src=/w3timages/blackline.gif width=33% height=2>

    If you increase the number of decimals that are displayed, you'll notice that round-off error is the cause of -0.00, but as Legare states you can remove it with conditional formatting or you can use a custom format like [>0.0001]#,##0.00;[<-0.0001]-#,##0.00;
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •