From: Reno on 26 Jan 2010 14:10 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 From: Gary''s Student on 26 Jan 2010 14:22 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 From: T. Valko on 26 Jan 2010 14:26 Works OK for me. Make sure calculation is set to automatic: In Excel 2007: Formulas tab>Calculation>Calculation Options>Automatic All other versions of Excel: Tools>Options>Calculation tab>Automatic>OK 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 news:695D744B-E607-43CA-92B9-0BC8216F0A17(a)microsoft.com...> 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 From: Reno on 26 Jan 2010 14:31 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 From: Reno on 26 Jan 2010 17:38 That worked, thanks! "T. Valko" wrote: > Works OK for me. Make sure calculation is set to automatic: > > In Excel 2007: > > Formulas tab>Calculation>Calculation Options>Automatic > > All other versions of Excel: > > Tools>Options>Calculation tab>Automatic>OK > > 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 > news:695D744B-E607-43CA-92B9-0BC8216F0A17(a)microsoft.com... > > 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 > > > . >