Prev: Taking data from a row moving it to another row and sorting it.
Next: Grouping Years in a Pivot table
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" <Reno(a)discussions.microsoft.com> 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" <Reno(a)discussions.microsoft.com> 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
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Taking data from a row moving it to another row and sorting it.
Next: Grouping Years in a Pivot table