HOW TO: RANK.EQ With an upper Limit...

[Darth269]

I'm trying to rank some stats. The problem is that I don't want to encourage blind use of a particular work related function, and so I only want to count up to 100%, anything over 100% should be considered as 100%.


However I also need to display the actual results still, so I cant just edit the raw data.


I have a table of scores... and a table of ranked scores next to it... (Below for the purpose of this explanation)





PERSON 1 76%
PERSON 2 202%
PERSON 3 62%
PERSON 4 71%
PERSON 5 79%
PERSON 6 100%
PERSON 7 122%
PERSON 8 69%
PERSON 9 95%
PERSON 10 76%
PERSON 11 132%
PERSON 12 81%
PERSON 13 150%

Using...

=IF(C68="","",(RANK.EQ(C68,C$68:C$80,1)))

For Person 1 and dragging down, I get ...


4
13
1
3
6
9
10
2
8
5
11
7
12

What I should get is a result showing all the 100% and over scores as the same...

4

9

1

3

6

9

9

2

8

5

9

7

9

 

[XOR LX]

Hi,

=IF(C68="","",(RANK.EQ(MIN(C68,1),C$68:C$80,1)))

Regards

 

[Darth269]

Thanks Xor LX, but this comes back as #NA for all results at or above 100%

 

[XOR LX]

Apologies. Try:

=IF(C68="","",(RANK.EQ(IF(C68>1,MAX(C$68:C$80),C68),C$68:C$80,1)))

Regards

 

[Darth269]

Apologies. Try:

=IF(C68="","",(RANK.EQ(IF(C68>1,MAX(C$68:C$80),C68),C$68:C$80,1)))

Regards

You Beauty, thanks Xor LX!

 

[Darth269]

I don't appear to have edit rights to the post, so I can't close it off... Can a mod do so?