Custom "conditional" format ?

[Don Wiss]

I find Excel does a pretty good job of hiding the help for custom cell
formats. I found part of it, but I was unable to find the help for when you
want the format to vary depending on the cell value. I tried this:

[>=100]#,##0;[<100]0.000;;"-"

Except when the value is zero I get 0.000, which means the ending of the
above isn't working. So how do I get no decimals when >100, three decimals
when less, and a dash when 0?

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[JE McGimpsey]

One way:

[>=100]#,##0;[>0]0.000;"-";

Note that values <0 will also get the "-", since XL can only have 3
numeric ranges in a custom format.

At least in XL04, the "About custom number formats" Help topic is pretty
complete.