Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round to thousands via custom format?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round to thousands via custom format?
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" wrote in message ups.com... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round to thousands via custom format?
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Round to thousands via custom format?
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 In article . com, znelson wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Excel I need to round up in tens of thousands | Excel Discussion (Misc queries) | |||
Format divide by 10 / single digit view: Custom? Text(Round( | Excel Discussion (Misc queries) | |||
Round to Thousands and x-foot the sum of the total | Excel Discussion (Misc queries) | |||
Format to Thousands and x-foot the sum | Excel Worksheet Functions | |||
How do I format cells to round to the nearest thousands without .. | Excel Discussion (Misc queries) |