Custom Number Formats and Conditions

[cpadude]

Hello. I am using a custom number format ($0.0,,). I want to be able to hide all Zero Values, or at least use a '-' character instead of getting a bunch of 0.0 numbers in my table. Is this possible while still retaining my custom number format ($0.0,,).

Thanks

[sweep]

Hi,


=IF(ISNUMBER(A1),A1,"")


Will get rid of your zeros

hth
dave

[cpadude]

Thanks for the reply.

I assume that you mean that I should put the IF() Statement in Conditional Formatting?

Is there a way to do this while only using the Custom Number Format and not have to use Conditional Formatting?

Thanks

[MrShorty]

No, he's suggesting using that as part of your worksheet formula. Whether it will work depends on whether the argument to the ISNUMBER function (A1 in his example) is an actual 0 or a blank cell. If A1 is blank or contains text, ISNUMBER will return false and the formula will evaluate to an empty string (""). If A1 contains the number 0 or a formula that evaluates to 0, ISNUMBER will return TRUE, and the cell will display the 0 with the assigned number format.

Perhaps a better approach is to include the conditional in your custom number format. If you search Excel's help for "number format codes," you'll get a list of number format codes you can use. In there, it describes how to include conditions in a number format code. So that a number format code of [>0]0.0;"-" will display numbers greater than 0 to one decimal place and anything else will be displayed as a "-".

The only thing you may have to look out for is if your cell contains a formula that should evaluate to 0, but, because of normal binary round off error, actually evaluates to something like +1E-15. +1E-15 is greater than 0, and could display as 0.0. If this is possible, then you may need to adjust either the condition or the formula to account for round off error.