Rank function

[MikeGInvAnalyst]

Hi Guys,

The formula below is only returning A and B and Not C

=IF(RANK(M2,$M$2:$M$3765,)/COUNT($M$2:$M$3765)<=1/3,"A",IF(RANK(M2,$M$2:$M$3765,)/COUNT($M$2:$M$3765)<=2/3,"B",IF(RANK(M2,$M$2:$M$3765,)/COUNT($M$2:$M$3765)>=3/3,"C")))

Any ideas ?

Many thanks

 

[ukmikeb]

Hi

Possibly :-

=LOOKUP(RANK(M2,$M$2:$M$3765,)/COUNT($M$2:$M$3765)*3,{0,1,2},{"A","B","C"})

hth

 

[MikeGInvAnalyst]

Thanks,

Unfortunately the same thing occurs,

the range of numbers in m2:m3765 is 0-3060,

However i can get it work if a -1 is introduced.

Many thanks

 

[ukmikeb]

Hi

Thanks for the feedback.

I did say possibly! Lol.

Pleased to have helped solve your problem.

Good luck with your project.