Formatting a number

[Michel Khennafi]

Good morning...

I have a situation where I would like the formatting of a % number to follow
the rules:
- if the number has no decimal then print the number as is for instance 98%
- if the number has more than one decimals, print the number with only one
decimal, for instance 98.17% becomes 98.2%

One of the goals is to avoid such displays as 98.00%

I tried to play with the format but never had a chance to figure it out...
has anyone handled this situation before? should I create a custom format?

Thanks so much for your assistance

Michel

 

[Bernie Deitrick]

Michel,

There is no formatting that will do that. You would need to use the worksheet change or worksheet
calculate event - the choice would depend on how your sheet is structured.

Copy the code below, right-click the sheet tab, select "View Code" and past e the code into the
window that appears.

As written, this will apply the format to cells in the range B1:B10.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myCell As Range
For Each myCell In Range("B1:B10")
If Int(myCell.Value * 100) = myCell.Value * 100 Then
myCell.NumberFormat = "0%"
Else
myCell.NumberFormat = "0.0%"
End If
Next myCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Range("B1:B10")
If Int(myCell.Value * 100) = myCell.Value * 100 Then
myCell.NumberFormat = "0%"
Else
myCell.NumberFormat = "0.0%"
End If
Next myCell
End Sub

 

[vezerid]

I can see only a VBA solution to this problem. I suggest the following
approach:

You use an event procedure for the Change event. In this procedure you
specify the range for which such behavior is desired. If you want you
can set (from Excel) the number format to % beforehand, to facilitate
editing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AutoFormatArea
Set AutoFormatArea = Range("A:A") '<<< Change Range specification here
If Intersect(Target, AutoFormatArea) Is Nothing Then Exit Sub
If Target.Value * 1000 Mod 10 = 0 Then
Target.NumberFormat = "0%"
Else
Target.NumberFormat = "0.0%"
End If
End Sub

To install:
1. Right click the sheet tab where you want this behavior. Choose View
Code...
2. The VBA IDE will appear. Paste the above code in the sheet module
window.

HTH
Kostis Vezerides