Number Formatting

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hi

I am currently using the following number format for a cell

#,##0 ;[Red]-#,##0

which gives the following result

-32,216

What I want is a number format that shows the number out
to 2 decimal places but only if there are decimal places
in the number. For example if the number was 32216.25 I
would want th number to be displayed as -32,216.25. But
if the number was -32216 I would want it displayed as -
32,216 and not -32,216.00

Is this possible and easy to do?

Thanks
 
If you want 123.4335324 to show as 123.43
and 123.00 to show as 123, I think you'll either need an event macro that
changes the number format or a helper cell that displays the number as text (use
the original cell for real calculations).

=IF(INT(A1)=A1,TEXT(A1,"#,##0;-#,##0"),TEXT(A1,"#,##0.00;-#,##0.00"))

But this loses the color.

So add format|conditional formatting with a formula like:
=--B1<0

(the first minus coerces the text to a number (but the opposite of what was
shown) and the second minus changes the sign back to the original.)

it's equivalent to:
=LEFT(B1,1)="-"

(I used A1 to hold the value and B1 to hold the "formatted number".)
Hi

I am currently using the following number format for a cell

#,##0 ;[Red]-#,##0

which gives the following result

-32,216

What I want is a number format that shows the number out
to 2 decimal places but only if there are decimal places
in the number. For example if the number was 32216.25 I
would want th number to be displayed as -32,216.25. But
if the number was -32216 I would want it displayed as -
32,216 and not -32,216.00

Is this possible and easy to do?

Thanks
Click to expand...
 
Hi,

I tried using the formula =round(A1,2), and using format "General', and
result is almost what you wanted, without the Comma.

André




Dave Peterson said:
If you want 123.4335324 to show as 123.43
and 123.00 to show as 123, I think you'll either need an event macro that
changes the number format or a helper cell that displays the number as text (use
the original cell for real calculations).

=IF(INT(A1)=A1,TEXT(A1,"#,##0;-#,##0"),TEXT(A1,"#,##0.00;-#,##0.00"))

But this loses the color.

So add format|conditional formatting with a formula like:
=--B1<0

(the first minus coerces the text to a number (but the opposite of what was
shown) and the second minus changes the sign back to the original.)

it's equivalent to:
=LEFT(B1,1)="-"

(I used A1 to hold the value and B1 to hold the "formatted number".)
Hi

I am currently using the following number format for a cell

#,##0 ;[Red]-#,##0

which gives the following result

-32,216

What I want is a number format that shows the number out
to 2 decimal places but only if there are decimal places
in the number. For example if the number was 32216.25 I
would want th number to be displayed as -32,216.25. But
if the number was -32216 I would want it displayed as -
32,216 and not -32,216.00

Is this possible and easy to do?

Thanks
Click to expand...
Click to expand...
 
Back
Top