Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage data with one entry extremely high | Charts and Charting in Excel | |||
Formula and or data entry into cell? | Excel Worksheet Functions | |||
want to make a formula for a data entry in the same cell in excel | Excel Discussion (Misc queries) | |||
Excel formula timestamp for a data entry of a referenced cell | Excel Worksheet Functions | |||
Formula and Data Entry in a Single Cell | Excel Discussion (Misc queries) |