# Is there a way to get the rank of a number that is not in the reference set?

#### JenniferMurphy

##### Well-known Member
The rank.eq and rank.avg functions return the #N/A error if the number is not in the reference set. Is there a way to get the (approximate) rank of such a number?

For example, is there a way to find the rank of "10" in the reference list 1, 4, 5, 9, 12, 13?

I see 3 possibilities:
1. Return the rank it would have if it were in the list. In this examples that would be 5.
2. Return average of the ranks of the numbers it would be between if it were in the set. In this example it would be 4.5.
3. Return prorated rank of the numbers it would be between if it were in the set. In this example, that would be 4.33.
Thanks

### 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

Excel Formula:
``=RANK.EQ(D1,(A1:A6,D1),1)``

1) try: =COUNTIF(\$A\$1:\$A\$6,"<="&D1)+1
with A1:A6 contains number list, D1 contains rank value
2) and 3): How come 4.5 and 4.33 manually?

Hi

Excel Formula:
``=RANK.EQ(D1,(A1:A6,D1),1)``
Of course. I should have thought of that. Thank you.

1) try: =COUNTIF(\$A\$1:\$A\$6,"<="&D1)+1
with A1:A6 contains number list, D1 contains rank value
2) and 3): How come 4.5 and 4.33 manually?
The problem arose in a sheet that I was using to track the number of games a player will win before losing N games, where N = 3. The sheet has a log of the number of games won in previous tries. The results of the active try is not added to the set until it is completed, but I wanted to show where it would be if it ended now.

Your solution, like the one from shift-del, puts the number into the reference set. In my option 2 & 3, the rank is calculated without actually adding the result to the set. If it is between two results, #2 averages the ranks of ones above and below. #3 prorates them. But now that I think about it, these really don't make sense.

Replies
8
Views
220
Replies
12
Views
411
Replies
1
Views
736
Replies
20
Views
596
Replies
1
Views
246

1,203,677
Messages
6,056,685
Members
444,883
Latest member
garyarubin

### 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