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 |
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 |
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 |
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 |
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 . |
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 . |
All times are GMT +1. The time now is 12:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com