Thread: Suppress zeros and errors (excel 97)

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

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

4. Re: Suppress zeros and errors (excel 97)

Aladin has excellent advice. I wondered about the #VALUE!: it usually means deal with this now! --Sam

5. Re: Suppress zeros and errors (excel 97)

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

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

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

8. 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;

Posting Permissions

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