Discussion Locked

Excel number formating...

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a column of cells that displays the results of several formulas. Sometimes the result of these formaulas is 0 but I don't want zero to appear. I've tried validation, and conditional formatting. Is there a way to custom format so that zero's do not appear but other numbers appear.

6 replies

by dklippert

The Custom format would be something like:
#,##0_);(#,##0_);
The trailing ?;? suppresses the Zero.

Or
You can go to Tools>Options>View and take the check mark out of the Zero values option.

Or
You could also wrap your formula in an If statement:
=If(A1-A2=0,"",A1-A2)

by tke402

Thanks, I tried the bottom formula rather than just going through tool>options>etc...

by tke402

DKlippert,
That worked perfectly. But I have another problem. I'm taking the average (as well as min max) of a column of numbers. I don't want the 0's to be counted in these calculations. I thought by making the zeros show as a blank space, it would not be used in the calculation. But it is. Is there anyway to exclude certain numbers in a formula?

by difster

It could be done programatically through a custome VBA function. Just define the array and make sure you exclude 0 values from the array.

Unfortunately, my VBA is a little rusty so I'm not in a position to give you the code but it can be done.

This discussion is closed to new replies.