Suppress zeros and errors (excel 97)

Topic

New Reply

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

Reply | Quote

Replies

=IF(ISERROR(A3),” “,IF(A3=0,” “,A3))

Substitute your equation in each place where there is an A3. HTH –Sam

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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;

Reply | Quote

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

Reply | Quote