Ranking a value outside of a selected range?

[surfdoc37]

Probably a simple thing but cannot figure it out.

I have a ranked list of numbers in a column.

I wish to compare numbers in a cell to see how they would rank in that ranked list, but I do not want to add them to the list.

For example, I could perhaps have a single column listing the number of HR hit by everyone who has ever played baseball ranked in descending order.

In a separate location I want to enter "99" (for instance) and learn that 99 HR would rank 493rd all time (or whatever it actually is).

RANK (RANK.EQ) does not seem to accept a number which is not in the ranked range so I'm stumped.

 

[shift-del]

RANK (RANK.EQ) does not seem to accept a number which is not in the ranked range so I'm stumped.

And you are absolutely right.

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

 

[Peter_SSs]

Possibly ..

=COUNTIF(A1:A22,">"&D1)+1

 

[surfdoc37]

And you are absolutely right.

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

Works great. If you have an extra minute, would you mind explaining why it works great?

 

[surfdoc37]

Possibly ..

=COUNTIF(A1:A22,">"&D1)+1

Also works and thanks.

 

[shift-del]

Works great. If you have an extra minute, would you mind explaining why it works great?

RANK.EQ() is one of only a few functions that accept a list of ranges - separated by a comma and enclosed in () - instead of one range.
All other rank functions also do and SMALL() and LARGE().