General discussion

Locked

Excel number formating...

By tke402 ·
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.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel number formating...

by DKlippert In reply to Excel number formating...

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)

Collapse -

Excel number formating...

by tke402 In reply to Excel number formating...

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

Collapse -

Excel number formating...

by tke402 In reply to Excel number formating...

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?

Collapse -

Excel number formating...

by difster In reply to Excel number formating...

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.

Collapse -

Excel number formating...

by tke402 In reply to Excel number formating...

Poster rated this answer

Collapse -

Excel number formating...

by tke402 In reply to Excel number formating...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums