How to only show decimals if there are any

[Myerse]

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.

[Vincnet.]

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

[tim m]

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

[Myerse]

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.

[dave0]

Use the General format. It's as simple as that.