Help needed with ranking formula

[Barry10]

Hi

I'm trying to find a formula to rank a group of numbers in a column with the following criteria:

>100 = 1
<100 and >20 = 2
<20 = 3

therefore all those numbers greater than 100 will be assigned a 1 and all those less than 100, but greater than 20 will get a 2. lastly all those results less than 20 will get a 3.

I have tried =LOOKUP(RANK(R2,$R$2:$R$100),{1,2,3}) but this formula will only give me one #1 and one #2 all the rest will be 3.

I have also tried IF((AND($R$2:$R$100<100,$R$2:$R$100>20)),2,0)+((AND($R$2:$R$100>100)),1,0)+((AND($R$2:$R$100<20)),3,0) but this returns an error

can anyone help?

[Sindhus]

How about

Please Login or Register  to view this content.

[Barry10]

Awesome! works great, thanks Sindhus