Percentage

[Valerie]

I have the following formula in a cell: =(A2/B2)*100 and
the cell is formatted for a percentage and it returns
1321%

Then I changed it to A2/B2 and the cell is formatted for
a percentage and it returns 13% - Which is correct - What
is happening?

Thanks

 

[JulieD]

Hi Valerie

the reason is that if you've used the % icon on the formatting toolbar it
doesn't just add the percent sign, it multiples the value in the cell by 100
as well - no idea why its designed like this, but it drives me crazy.

Cheers
JulieD

 

[Frank Kabel]

Hi
this is just correct
1% = 0.01
so if you format your data as percentage no need for multiplying with
100

 

[Frank Kabel]

Hi Julie
IMHO that is just how a 'percent' is defined. 100%=1

 

[JulieD]

Hi Frank

the problem i find is that it's not just a "format" icon (it does maths as
well) ... whereas the others in that section on the toolbar are - i find
that confusing - especially when training newbies.

Cheers
JulieD

 

[Gord Dibben]

Julie

For your own use.........

Assign this code to the % Button.

Sub NumToPercent()
Dim c As Range
For Each c In Selection
a = c.Value
If IsNumeric(a) Then
If a <> 0 Then a = a / 100
c.Value = a
c.Style = "Percent"
End If
Next
End Sub

Gord Dibben Excel MVP

 

[Frank Kabel]

Hi
for me it would do 'math' if it would show 16.34 as 16.34 percent
I could understand that this could cause issues while training newbies
BUT still Excel is mathematical correct doing this and the problem then
is more the underlying math but Excel's formating <vbg>

 

[JulieD]

Hi Gord

thanks for this.

Cheers
JulieD

Julie

For your own use.........

Assign this code to the % Button.

Sub NumToPercent()
Dim c As Range
For Each c In Selection
a = c.Value
If IsNumeric(a) Then
If a <> 0 Then a = a / 100
c.Value = a
c.Style = "Percent"
End If
Next
End Sub

Gord Dibben Excel MVP