number formatting - conditional decimal point

Is there a way to make a custom number format so that decimals are rounded
to a certain number of digits, but numbers with no decimals are shown
without the decimal point.

i.e. if I use the format 0.## or #.##:
8.237 will show as "8.24"
but 8 will show as "8."
whereas I want 8 to show as "8"

Is this possible?

Thanks.

On Fri, 14 Oct 2005 12:32:12 -0400, "Ric" <[email protected]> wrote:

>Is there a way to make a custom number format so that decimals are rounded
>to a certain number of digits, but numbers with no decimals are shown
>without the decimal point.
>
>i.e. if I use the format 0.## or #.##:
>8.237 will show as "8.24"
>but 8 will show as "8."
>whereas I want 8 to show as "8"
>
>Is this possible?
>
>Thanks.
>

It is possible with VBA. It is not possible with formatting, so far as I know.


--ron

Only with VBA, afaik. Something like this ...


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [A1:B2]) Is Nothing Then Exit Sub
If Int(Target) = Target Then
Target.NumberFormat = "#"
Else
Target.NumberFormat = "#.#"
End If
End Sub


To use, right click your sheet tab name and select View Code. Then
copy/paste the above into the right pane, adjusting the range ([A1:B2]) to
what you desire. Then hit Alt + Q to return to Excel.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



"Ric" <[email protected]> wrote in message
news:%[email protected]...
> Is there a way to make a custom number format so that decimals are rounded
> to a certain number of digits, but numbers with no decimals are shown
> without the decimal point.
>
> i.e. if I use the format 0.## or #.##:
> 8.237 will show as "8.24"
> but 8 will show as "8."
> whereas I want 8 to show as "8"
>
> Is this possible?
>
> Thanks.
>
>

If you leave the cells formatted as General and make the column just wide enough
to hold those 2 decimals, then you'll see what you want--but if you autofit the
columnwidth, you'll be mad!

Maybe using a formula, too:
=round(a1,2)

This might be useful if the 8.237 were the results of a formula.
Just make it: =round(yourformula,2)

Ric wrote:
>
> Is there a way to make a custom number format so that decimals are rounded
> to a certain number of digits, but numbers with no decimals are shown
> without the decimal point.
>
> i.e. if I use the format 0.## or #.##:
> 8.237 will show as "8.24"
> but 8 will show as "8."
> whereas I want 8 to show as "8"
>
> Is this possible?
>
> Thanks.

--

Dave Peterson

Well, combining the last 2 suggestions something like this might work:

=IF(INT(A3)=A3,A3,ROUND(A3,2))

entered into a different column formatted as general. The macro approach
isn't great because you'd need to run it whever you make a change. This one
is automatic but needs the extra column. I can hide the original column but
then need to unhide it if I want to make changes. I suppose I can have the
input columns on one sheet, and use a separate sheet for formatted output.
If I need to do a lot of data changes I can have a different window open for
each sheet...

Thanks for the suggestions.
Ric


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> If you leave the cells formatted as General and make the column just wide
> enough
> to hold those 2 decimals, then you'll see what you want--but if you
> autofit the
> columnwidth, you'll be mad!
>
> Maybe using a formula, too:
> =round(a1,2)
>
> This might be useful if the 8.237 were the results of a formula.
> Just make it: =round(yourformula,2)
>
> Ric wrote:
>>
>> Is there a way to make a custom number format so that decimals are
>> rounded
>> to a certain number of digits, but numbers with no decimals are shown
>> without the decimal point.
>>
>> i.e. if I use the format 0.## or #.##:
>> 8.237 will show as "8.24"
>> but 8 will show as "8."
>> whereas I want 8 to show as "8"
>>
>> Is this possible?
>>
>> Thanks.
>
> --
>
> Dave Peterson

I don't think you're getting any benefit by doing that first check.

=round(a3,2)

would return a whole number if a3 were a whole number.

Ric wrote:
>
> Well, combining the last 2 suggestions something like this might work:
>
> =IF(INT(A3)=A3,A3,ROUND(A3,2))
>
> entered into a different column formatted as general. The macro approach
> isn't great because you'd need to run it whever you make a change. This one
> is automatic but needs the extra column. I can hide the original column but
> then need to unhide it if I want to make changes. I suppose I can have the
> input columns on one sheet, and use a separate sheet for formatted output.
> If I need to do a lot of data changes I can have a different window open for
> each sheet...
>
> Thanks for the suggestions.
> Ric
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > If you leave the cells formatted as General and make the column just wide
> > enough
> > to hold those 2 decimals, then you'll see what you want--but if you
> > autofit the
> > columnwidth, you'll be mad!
> >
> > Maybe using a formula, too:
> > =round(a1,2)
> >
> > This might be useful if the 8.237 were the results of a formula.
> > Just make it: =round(yourformula,2)
> >
> > Ric wrote:
> >>
> >> Is there a way to make a custom number format so that decimals are
> >> rounded
> >> to a certain number of digits, but numbers with no decimals are shown
> >> without the decimal point.
> >>
> >> i.e. if I use the format 0.## or #.##:
> >> 8.237 will show as "8.24"
> >> but 8 will show as "8."
> >> whereas I want 8 to show as "8"
> >>
> >> Is this possible?
> >>
> >> Thanks.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Yes, of course you're right. duh...

Ric

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I don't think you're getting any benefit by doing that first check.
>
> =round(a3,2)
>
> would return a whole number if a3 were a whole number.
>
> Ric wrote:
>>
>> Well, combining the last 2 suggestions something like this might work:
>>
>> =IF(INT(A3)=A3,A3,ROUND(A3,2))
>>