decimal point

[legepe]

Can anyone tell me why when I enter for eg: 7.92 into a cell and then change it to a % it displays 792% and how can I change the config?
If I make the cells into a format of % and then enter the number manually it is displayed and seen by excel correctly.
But, the cells where I need to convert them to a formula have formulas
How can I resolve this problem?
Thanks
legepe

[legepe]

Sorry!
I meant convert to a % not formula

[Bob Phillips]

If a cell is formatted as General, when you input 7.92, it is 7.92. Converting that to a percentage is 792 percent (1 is 100%).

However, if the cell is pre-formatted as percent, Excel recognises that and changes the value to .0792, which formatted shows as 7.92%.

[legepe]

I understand what you mean but i think I am going a little mad!!
I never remember having this problem before when I have worked with similar formulas, etc..
Is there a general setting somewhere to config.. this? O am I loosing my way?
Thanks
legepe

[Bob Phillips]

Always been that way AFAIK.

[malik641]

For a work-around you could use a custom cell format, like:

0.00"%"

Just a thought :)

[mdmackillop]

Just a thought :)
Time for second thoughts, Joseph
Try multiplying a figure by your "format" solution. :eek:
Regards
MD

[malik641]

Time for second thoughts, Joseph
Try multiplying a figure by your "format" solution. :eek:
Regards
MD
Okay, second thoughts....

I guess it's time for some coding (cause I can't figure out a method using Custom Number Formats). Since legepe said it's entered data first, then formatted, he could use this:

Sub ChangeFormat()
'Select the range you need to change, then run this macro
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
With cell
.Value = .Value / 100
.NumberFormat = "0.00%"
End With
Next
End Sub
I don't like this solution...but it's a "quick fix" I guess.....I wish I could figure something out with Custom number formats, though.

Sorry about the other post, my bad.

[mdmackillop]

Hi Joseph,
You could use your code in a change event. (Column G in this case)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 7 Then
With Target
.Value = .Value / 100
.NumberFormat = "0.00%"
End With
End If

Application.EnableEvents = True
End Sub

[malik641]

yeah, it could be done that way too.

Don't forget
Application.EnableEvents = True
at the end :)

[mdmackillop]

Fixed!

[malik641]

Nice!

This may be overboard...but how about:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, cell As Range

Set rng = Range(Cells(Target.Row, "G"), Cells(Selection(Target.Rows.Count).Row, "G"))

Application.EnableEvents = False
For Each cell In rng
If cell.NumberFormat <> "0.00%" And cell.Value <> "" Then
With cell
.Value = .Value / 100
.NumberFormat = "0.00%"
End With
End If
Next
Application.EnableEvents = True
End Sub