Entry of data in formula bar ignores cell percentage formatting
 

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

Entry of data in formula bar ignores cell percentage formatting
 

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" wrote in message
...
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

Entry of data in formula bar ignores cell percentage formatting
 

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" wrote in message
...
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" wrote in message
...
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

Entry of data in formula bar ignores cell percentage formatting
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Entry of data in formula bar ignores cell percentage formatting
 

Hi Julie --

There is no code running behind the cell in question.

Larry

"JulieD" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Entry of data in formula bar ignores cell percentage formatting
 

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

"L Mehl" wrote in message
...
Hi Julie --

There is no code running behind the cell in question.

Larry

"JulieD" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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