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

[JenniferMurphy]

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

 

[shift-del]

Hi

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

 

[bebo021999]

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?

 

[JenniferMurphy]

Hi

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

Of course. I should have thought of that. Thank you.

 

[JenniferMurphy]

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.