I need calculated values to be displayed without any trailing zeros. For
example:
if the result is 12.0, I need it displayed as 12
if the result is 5.50, I need it displayed as 5.5
but... in a worksheet of calculated values, I need the values displayed to
the hundredth's place, as long as there are no trailing zeros, so I can't
just format the cells to one placeholder.

Can this be done? THANX.

I can't figure out how to display an integer without a trailing decimal, but
you can handle the decimal values with a custom format of

0.##



"Robin" wrote:

I need calculated values to be displayed without any trailing zeros. For
example:
if the result is 12.0, I need it displayed as 12
if the result is 5.50, I need it displayed as 5.5
but... in a worksheet of calculated values, I need the values displayed to
the hundredth's place, as long as there are no trailing zeros, so I can't
just format the cells to one placeholder.

Can this be done? THANX.

Scratch that last comment -

If all your numbers are integers or have no more than 2 decimal places, just
use the General format. If the numbers potentially exceed to decimal places,
you'll need to ROUND them or TRUNCate them.


"Robin" wrote:

I need calculated values to be displayed without any trailing zeros. For
example:
if the result is 12.0, I need it displayed as 12
if the result is 5.50, I need it displayed as 5.5
but... in a worksheet of calculated values, I need the values displayed to
the hundredth's place, as long as there are no trailing zeros, so I can't
just format the cells to one placeholder.

Can this be done? THANX.

Robin, (there IS a solution)

Background:
When formatting numeric cells, Excel tries to line up the decimal point, so
all
numbers in a column are more easy to read. Because of this, when you change
cell number formats to show 3-places, you will ALWAYS get ONLY three places.
The numbers with MORE than 3-decimal places will be rounded to the nearest
value, and numbers like 2.5 would show with trailing zeros (as 2.500) so
things
line up nicely.

ANSWER: You wanted to set the number of decimals, but have NO trailing zero.
Don't just set the number of decimal places in the normal fashion...
With your cells selected, goto the format dialogue... (use Ctrl-1 or
Alt_O--E)
then pick the number_tab (on left). Next go to the scrolling set of options
to
the left, and scroll all the way down to the bottom. You will find an
option that
says "Custom". This will allow you to enter your exact format.

OPTION-#1:
In the format section, enter 0.### (using trailing pound signs)

This will result in UP-TO three decimal places being displayed, but NOT
trailing
zeros... so 36="36." 342.81="342.81" and 6.23465="6.235"
Note that there is always a decimal, even if the value has no fraction...
I don't know how do format a conditional deciml point.

OPTION-#2:
In the format section, enter 0.??? (using trailing question marks)

This will result in UP-TO three decimal places being displayed, but trailing
spaces
instead of trailing zeros... so 36="36. " 342.81="342.81 " and
6.23465="6.235"
This keeps all numbers lined up nicely, but has no trailing zero displayed.

I hope it helps... (add response & feedback, so I'll know you
read it)
Joseph in Atlanta


"Robin" wrote:

I need calculated values to be displayed without any trailing zeros. For
example:
if the result is 12.0, I need it displayed as 12
if the result is 5.50, I need it displayed as 5.5
but... in a worksheet of calculated values, I need the values displayed to
the hundredth's place, as long as there are no trailing zeros, so I can't
just format the cells to one placeholder.

Can this be done? THANX.

Option 1 did the trick perfectly! Thank you very much.
Robin

"Joseph in Atlanta" wrote:

Robin, (there IS a solution)

Background:
When formatting numeric cells, Excel tries to line up the decimal point, so
all
numbers in a column are more easy to read. Because of this, when you change
cell number formats to show 3-places, you will ALWAYS get ONLY three places.
The numbers with MORE than 3-decimal places will be rounded to the nearest
value, and numbers like 2.5 would show with trailing zeros (as 2.500) so
things
line up nicely.

ANSWER: You wanted to set the number of decimals, but have NO trailing zero.
Don't just set the number of decimal places in the normal fashion...
With your cells selected, goto the format dialogue... (use Ctrl-1 or
Alt_O--E)
then pick the number_tab (on left). Next go to the scrolling set of options
to
the left, and scroll all the way down to the bottom. You will find an
option that
says "Custom". This will allow you to enter your exact format.

OPTION-#1:
In the format section, enter 0.### (using trailing pound signs)

This will result in UP-TO three decimal places being displayed, but NOT
trailing
zeros... so 36="36." 342.81="342.81" and 6.23465="6.235"
Note that there is always a decimal, even if the value has no fraction...
I don't know how do format a conditional deciml point.

OPTION-#2:
In the format section, enter 0.??? (using trailing question marks)

This will result in UP-TO three decimal places being displayed, but trailing
spaces
instead of trailing zeros... so 36="36. " 342.81="342.81 " and
6.23465="6.235"
This keeps all numbers lined up nicely, but has no trailing zero displayed.

I hope it helps... (add response & feedback, so I'll know you
read it)
Joseph in Atlanta


"Robin" wrote:

I need calculated values to be displayed without any trailing zeros. For
example:
if the result is 12.0, I need it displayed as 12
if the result is 5.50, I need it displayed as 5.5
but... in a worksheet of calculated values, I need the values displayed to
the hundredth's place, as long as there are no trailing zeros, so I can't
just format the cells to one placeholder.

Can this be done? THANX.

Try this formula where 12.0 is in A1.
=ROUND(A1,2)
This will give 12 or 5.5 where A1 is 5.50.
Or replace A1 in the formula to be the formula you use to calculate the
values, to save having an extra cell and formula. Note that unlike cell
formatting which doesn't change the value but just shows it
differently, this formula can actually reduce the accuracy of the value
by rounding say 5.316454845 to 5.32. Therefore, don't use formulas that
in turn refer to the result of the round formula, unless you just want
it to use 5.32.
To give 100 decimal places (without trailing zeros), change it to
=round(a1,100).
If I haven't understood correctly, please explain further.
Clive


Robin Wrote:
I need calculated values to be displayed without any trailing zeros.
For
example:
if the result is 12.0, I need it displayed as 12
if the result is 5.50, I need it displayed as 5.5
but... in a worksheet of calculated values, I need the values displayed
to
the hundredth's place, as long as there are no trailing zeros, so I
can't
just format the cells to one placeholder.

Can this be done? THANX.


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=524964