Percentage formatting / dec places

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,261
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,261
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079

ADVERTISEMENT

Whatever it is, I call it VERY ANNOYING !
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,632
Messages
5,832,774
Members
430,167
Latest member
Gogogoben

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top