Round to thousands via custom format?

  • Thread starter Thread starter znelson
  • Start date Start date
Z

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!
 
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)
 
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.
 
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>
 
Back
Top