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

#### Darth269

##### New Member
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...
Code:
``=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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi,

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

Regards

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

Apologies. Try:

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

Regards

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!

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

Replies
25
Views
749
Replies
6
Views
173
Replies
10
Views
359
Replies
8
Views
96
Replies
11
Views
250

1,206,826
Messages
6,075,083
Members
446,118
Latest member
Alqahtanir

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back