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)

 

[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?

 

[lj]

Maybe...

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

You really meant less than 1%, right?

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.

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!

Maybe...

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

You really meant less than 1%, right?

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]

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

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!

Maybe...

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

You really meant less than 1%, right?

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.

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

lj wrote:

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

 

[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.

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

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!

Maybe...
[=0]"-";[<0.1].0%;#%
You really meant less than 1%, right?
lj wrote:
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.
You can use a custom number format:
Format|cells|number tab|custom category
#,###.000;-#,###.000;"-";@
(positive;negative;0;text)
lj wrote:
Hi, I want to set up a format for numbers (not accounting) thatshows
a dash - instead of a 0 when the cell value equals 0.  Is there a
special format for this?  Thanks.

 

[Dave Peterson]

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

Custom number formats don't allow that many options.

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.

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

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!

Maybe...

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

You really meant less than 1%, right?

lj wrote:

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.

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

lj wrote:

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

Dave Peterson