How to only show decimals if there are any
I want my numbers to be formatted as follows: 423 15 (2.5) .5 10 (.5) (2) (23) 1,516 It is a FTE report, so most numbers are whole numbers, but some departments have .5 for part-time employees. When I use General formatting, the negative numbers show as -2, instead of (2) and the decimals show as 0.5 instead of .5. I can live with this, but was wondering how to format to get above results. I have tried the following custom formatting, but it puts a decimal after an integer, so 423 is 423. and (2) is (2.) _(* #,###.##_);_(* (#,###.##);_(* "-"??_);_(@_) Any ideas? Thanks for your help. -- Myerse ------------------------------------------------------------------------ Myerse's Profile: http://www.excelforum.com/member.php...o&userid=35719 View this thread: http://www.excelforum.com/showthread...hreadid=555029 |
How to only show decimals if there are any
I often use this custom format: #,###.0_);(#,###.0);-_)
and it works perfectly... Does it for you? -- KR V. "Myerse" wrote: I want my numbers to be formatted as follows: 423 15 (2.5) .5 10 (.5) (2) (23) 1,516 It is a FTE report, so most numbers are whole numbers, but some departments have .5 for part-time employees. When I use General formatting, the negative numbers show as -2, instead of (2) and the decimals show as 0.5 instead of .5. I can live with this, but was wondering how to format to get above results. I have tried the following custom formatting, but it puts a decimal after an integer, so 423 is 423. and (2) is (2.) _(* #,###.##_);_(* (#,###.##);_(* "-"??_);_(@_) Any ideas? Thanks for your help. -- Myerse ------------------------------------------------------------------------ Myerse's Profile: http://www.excelforum.com/member.php...o&userid=35719 View this thread: http://www.excelforum.com/showthread...hreadid=555029 |
How to only show decimals if there are any
I played around with your problem and managed to get it to look like you want
but it takes a bit of mucking about. I created this formula which will fool the a number that is a decimal into thinking it's a whole: =IF(RIGHT(A7,2)=0.5,A7,ROUNDDOWN(A7,1)) I then made the following custom format: General;(General) (You would have to insert a column and put this formula in the column. In my test formula I started on cell A7, you would of course but the 1st cell your data is in that place and then copy the formula down.) "Myerse" wrote: I want my numbers to be formatted as follows: 423 15 (2.5) .5 10 (.5) (2) (23) 1,516 It is a FTE report, so most numbers are whole numbers, but some departments have .5 for part-time employees. When I use General formatting, the negative numbers show as -2, instead of (2) and the decimals show as 0.5 instead of .5. I can live with this, but was wondering how to format to get above results. I have tried the following custom formatting, but it puts a decimal after an integer, so 423 is 423. and (2) is (2.) _(* #,###.##_);_(* (#,###.##);_(* "-"??_);_(@_) Any ideas? Thanks for your help. -- Myerse ------------------------------------------------------------------------ Myerse's Profile: http://www.excelforum.com/member.php...o&userid=35719 View this thread: http://www.excelforum.com/showthread...hreadid=555029 |
How to only show decimals if there are any
Thanks for your responses!! Vince - the custom format you specify puts a zero after the end of a whole number, so if a department has 10 people, it would show up as 10.0. Since most departments have no part-time people, I did not want to burden the report with lots of extra .0's. Tim - wow! I SHOULD put some crazy stuff in this report like your formula since the person who created these worksheets has forced me to learn more excel than I cared to know. Unfortunately, this report has MANY columns and worksheets that I want to have these formats in, so I don't think putting in an extra column for each column that I want to format would be feasible. However, I learned another Excel function - "RIGHT". I had to look that up because I wasn't sure what that was doing. Thanks again for your help. -- Myerse ------------------------------------------------------------------------ Myerse's Profile: http://www.excelforum.com/member.php...o&userid=35719 View this thread: http://www.excelforum.com/showthread...hreadid=555029 |
All times are GMT +1. The time now is 04:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com