Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign text to numeric value
have weekly store gross profit report(s) and want to assign a letter based on
a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 7,6,5,4,3,2,1 respectively...have tried =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7", "6","5","4","3","2",1"}) which gives the same rating for all stores. is cntl-shft required with the use of braces { }? thx |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign text to numeric value
Will something like:
=ROUNDUP((7000-A2)/1000,0) work?? -- Gary''s Student - gsnu201001 "Reno" wrote: have weekly store gross profit report(s) and want to assign a letter based on a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 7,6,5,4,3,2,1 respectively...have tried =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7", "6","5","4","3","2",1"}) which gives the same rating for all stores. is cntl-shft required with the use of braces { }? thx |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign text to numeric value
Works OK for me. Make sure calculation is set to automatic:
In Excel 2007: Formulas tabCalculationCalculation OptionsAutomatic All other versions of Excel: ToolsOptionsCalculation tabAutomaticOK You also might want to remove the quotes from around the numbers: =LOOKUP(A1,{0,1000,2000,3000,4000,5000,6000},{7,6, 5,4,3,2,1}) Quoting numbers makes them TEXT. -- Biff Microsoft Excel MVP "Reno" wrote in message ... have weekly store gross profit report(s) and want to assign a letter based on a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 7,6,5,4,3,2,1 respectively...have tried =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7", "6","5","4","3","2",1"}) which gives the same rating for all stores. is cntl-shft required with the use of braces { }? thx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign text to numeric value
it might, but the ranges and/or designation(s) (1... goes to A..) or something.
tried ={0,"7", 1.1,"6"... } cntl-shft enter as an array, but this also gave incorrect/inconsistent error too. thx "Gary''s Student" wrote: Will something like: =ROUNDUP((7000-A2)/1000,0) work?? -- Gary''s Student - gsnu201001 "Reno" wrote: have weekly store gross profit report(s) and want to assign a letter based on a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 7,6,5,4,3,2,1 respectively...have tried =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7", "6","5","4","3","2",1"}) which gives the same rating for all stores. is cntl-shft required with the use of braces { }? thx |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign text to numeric value
That worked, thanks!
"T. Valko" wrote: Works OK for me. Make sure calculation is set to automatic: In Excel 2007: Formulas tabCalculationCalculation OptionsAutomatic All other versions of Excel: ToolsOptionsCalculation tabAutomaticOK You also might want to remove the quotes from around the numbers: =LOOKUP(A1,{0,1000,2000,3000,4000,5000,6000},{7,6, 5,4,3,2,1}) Quoting numbers makes them TEXT. -- Biff Microsoft Excel MVP "Reno" wrote in message ... have weekly store gross profit report(s) and want to assign a letter based on a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 7,6,5,4,3,2,1 respectively...have tried =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7", "6","5","4","3","2",1"}) which gives the same rating for all stores. is cntl-shft required with the use of braces { }? thx . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign text to numeric value
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Reno" wrote in message ... That worked, thanks! "T. Valko" wrote: Works OK for me. Make sure calculation is set to automatic: In Excel 2007: Formulas tabCalculationCalculation OptionsAutomatic All other versions of Excel: ToolsOptionsCalculation tabAutomaticOK You also might want to remove the quotes from around the numbers: =LOOKUP(A1,{0,1000,2000,3000,4000,5000,6000},{7,6, 5,4,3,2,1}) Quoting numbers makes them TEXT. -- Biff Microsoft Excel MVP "Reno" wrote in message ... have weekly store gross profit report(s) and want to assign a letter based on a range of $0, 1000,2000,3000,4000,5000, 6000 which would return 7,6,5,4,3,2,1 respectively...have tried =lookup(A1,{0,1000,2000,3000,4000,5000,6000},{"7", "6","5","4","3","2",1"}) which gives the same rating for all stores. is cntl-shft required with the use of braces { }? thx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assign numeric value to letters and sum with other numbers | Excel Worksheet Functions | |||
How do I assign a numeric value to a letter | Excel Worksheet Functions | |||
How do I assign a numeric value to text? IE cell = yes then 1 | Excel Discussion (Misc queries) | |||
Can I assign a numeric value to words in Excel? | Excel Discussion (Misc queries) | |||
How do I assign a numeric value to a text letter | Excel Discussion (Misc queries) |