Percentage formatting / dec places

[staticbob]

Guys,

Simple one... I have a cell formatted to a percentage. When the users enter .92 it displays 92%

How can I format this cell so it will not actually convert the entry to a &age, just display it. If the user enters 0.85, it needs to display 0.85%, not 85% as it does at the moment.

Cheers
Bob

 

[GlennUK]

Have them enter it as 0.85% ( i.e. physically type the % with the 0.85 ) ... otherwise 0.85 is 85% percent, and can understand why Excel does the automatic conversion. I'll have a think about alternatives just in case typing the % sign is a no go.

 

[Joe4]

Mathematically speaking, .85 = 85%. It sounds like you want to actually enter in the percentage, not the value. We can do that with a macro that will divide all entries by 100.

Right click on the sheet tab name, click View Code, and paste this code in the following window. This code is written so that any entry in column F will automatically be divided by 100. Adjust the range accordingly. This macro will be triggered automatically upon entry.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
'   Enter range to apply this to
    Set myRange = Columns("F:F")
    
'   Divide numeric entries in defined range by 100
    If Not Intersect(Target, myRange) Is Nothing Then
        Application.EnableEvents = False
        If IsNumeric(Target) Then Target = Target / 100
        Application.EnableEvents = True
    End If
    
End Sub

Also, make sure that you have your column already formatted as a percentage with the correct number of decimals (2?).

 

[staticbob]

Thanks JM,

I hadn't contemplated a macro as I thought there maybe a formatting solution. I know that 0.85 is mathematically 85%, but trying to explain that to builders, and then tell them they need to enter the % sign is a no go !

I'll code it !

Thanks
Bob

 

[staticbob]

Actually, if they enter .92, it displays 92%, if they enter 0.92 it will display 0.92%. How can the leading zero make that much difference ! LOL

Thanks
Bob

 

[Joe4]

Actually, if they enter .92, it displays 92%, if they enter 0.92 it will display 0.92%. How can the leading zero make that much difference ! LOL

Strange, my computer doesn't exhibit that behavior. Wonder if it is one of those subtle differences between the US and European versions of Excel.

 

[staticbob]

Whatever it is, I call it VERY ANNOYING !

 

[GlennUK]

I get that behaviour too ... UK, Excel 97.

 

[staticbob]

I'm on Excel 2003, UK. But it also the same on 2000.