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

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

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

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