Formatting

[lj]

Hi, I want to set up a format for numbers (not accounting) that shows
a dash - instead of a 0 when the cell value equals 0. Is there a
special format for this? Thanks.

[Dave Peterson]

You can use a custom number format:
Format|cells|number tab|custom category

#,###.000;-#,###.000;"-";@
(positive;negative;0;text)

--

Dave Peterson

[lj]

Thanks, this is very helpful. I'm dealing with percentages, is there
code I can use if I want to show 1 decimal place if the number is less
than 1, for example 0.4% but otherwise only whole numbers, for example
4%. Thanks.

[Dave Peterson]

Maybe...

[=0]"-";[<0.1].0%;#%

You really meant less than 1%, right?

--

Dave Peterson

[lj]

yes, less than 1%. I'll try that. Thanks!

[lj]

I tried this formula you gave me but it isn't working correctly. What
I'm looking for is if the percent is less than 1 but greater than 0 ,I
want to show one decimal place (example 0.2%), but if it's greater
than one percent I want to just show the whole number percent with no
decimal (example 4%). I want to show the same thing, for negative
numbers. Also, in the formula below what does the [ ] do and what
does the = do? Thanks!

[Dave Peterson]

[=0]"-";[<0.01]0.0%;#%

--

Dave Peterson

[lj]

Thanks! This works well when the % is positive. How do I adjust it
also for when the number is negative? For example, if the % is
between -1 and 0 I would want to show -0.3% but if it's less than -1
I don't want to show a decimal (example -5%). Thanks.

[Dave Peterson]

I don't think you can do it with formatting.

Custom number formats don't allow that many options.

--

Dave Peterson