Entry of data in formula bar ignores cell percentage formatting

[L Mehl]

Hello --

My user was complaining that entering a percentage in a percentage-formatted
cell was resulting in 100 times the value he entered.

His method of entry was to enter the value in the formula bar while the
target cell was hilighted (does not seem like an efficient way to enter
data) which appears to ignore the cell format: Number > Percentage.

But ... the customer is always right.

Is there a way to have the cell formatting apply when cell data entry is via
the formula bar?

Thanks for any help.

Larry Mehl

 

[JulieD]

Hi Larry

bit confused here, if he formatted the cell using the % on the toolbar then
the number will be multiplied by 100 and the % sign added when he enters a
number - whether entered through the formula bar or directly into the cell -
(i wrote a post about a week ago complaining about this. This also
happens when you format the cell using format cells / number / percentage
(and if you have a look on this screen when you choose percentage it clearly
states that "percentage format multiplies the cell value by 100 and displays
the result with a percent symbol". )

so alternatives
1) divide the number by 100 when he enters it
2) type a % after the number and leave the cell format as general.
3) if he has lots of these to do he could choose tools / options / edit -
check fixed decimal places, set it to 2 then enter his "normal" numbers into
% formatted cells and then uncheck this when he's finished.
4) Gord Dibben posted this code the other day (in response to my complaining
- thanks Gord)
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

this code can be added to personal.xls (in a module - post back if you need
details on how to do this), then create a toolbar icon to use to activate
the code & remove the built-in % one.

Hope this helps
Cheers
JulieD

 

[L Mehl]

Hi Julie --

Thanks for the code. It will help me control other entry cells.

I should have been more clear in describing the problem.

I designed the workbook in Excel 2000; users have Excel 2003.

I was the one that formatted the cell, using context menu > Format Cells >
Number > Percentage ...

Entry methods work differently:
a) in formula bar - user must add a "%" sign after the number, or it will be
multiplied by 100
b) in the cell - does not require including a trailing "%"; .5 is
interpreted as 50%, 5 as 5%, 50 as 50%, 500 as 500%.

I am tempted to simply disable the Formula bar, but I will first attempt to
train users on where to enter data.

Larry

 

[JulieD]

b) in the cell - does not require including a trailing "%"; .5 is

interpreted as 50%, 5 as 5%, 50 as 50%, 500 as 500%.

.... have you got code running behind this because i can't see how entering
..5 can give you 50% AND 50 can give you 50% without some sort of
intervention (... i don't however, have ver 2000 on my system any more to
check the native behaviour of format / cells / numbers / percentage - but
AFAIK it hasn't been changed).

Cheers
JulieD

 

[L Mehl]

Hi Julie --

There is no code running behind the cell in question.

Larry

 

[JulieD]

Hi Larry

just to confirm -
if you type 0.5 in the cell you get 50%
and if you type 50 in the cell you get 50%

i truely can't see how that works.

Cheers
JulieD