number formatting - conditional decimal point

[Ric]

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.

 

[Ron Rosenfeld]

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

 

[Zack Barresse]

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

 

[Dave Peterson]

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]

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]

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]

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

Ric

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.