Round to thousands via custom format?

[znelson]

I need to round numbers via a custom format. I'm using:

##,##0,_);[Red](##,##0,)

That gives me exactly what I need as long as the number is > 1000 or <
-1000 .. there's a problem when the number is only 3 digits long, eg.
500 or -500 .. in which case it shows up as 0 and (0) respectively.

I need help modifying this format to present 3 or 2 or 1 digit numbers
like this: .5 and (.5) respectively. And the catch is, I only want to
see the decimal point when it's necessary, so 1000 would show up as 1
and not 1.0

Any ideas? I've seen numerous posts about rounding to thousands but
none that address this specific issue. Thanks in advance!

 

[Bob Phillips]

I don't think it can be done, it can show 1 (if 500 shows as 1, 400 as 0)),
1.0 or 1., but not and showing 500 as .5.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[znelson]

Thanks Bob, I think we're just going to leave the format alone and set
the column to always show a single decimal place, that way 500 shows
as .5 and 1000 shows as 1.0 .. done deal.

 

[JE McGimpsey]

You can't do it directly, but if you can do without the parens, one way:

Format/Cells/Number/Custom:

[>=1000]#,##0,;[>-1000]0.###,;#,##0,

Format/Conditional Format:
CF1: Cell value is less than 0
Format1: <font>/<red>