Forum Discussion
Ranking
I am trying to rank the following the following numbers. 40,39,41,38,39,37,23,35,25,35,30,30,33,31,35.5,29,30,32,33.5,40, and 37.5. When I attempt to rank 39 using the function =RANK(B3,B3:B26) I com...
Hi Thomas,
I'd suggest
=RANK(B3,$B$3:$B$23,0) -
(
COUNTIF($B$3:$B$23, ">"&B3)-
SUMPRODUCT(($B$3:$B$23>B3)*1/COUNTIF($B$3:$B$23,$B$3:$B$23))
)(range is from B3 to B23).
COUNTIF returns number of values greater than current one. SUMPRODUCT - number of unique values in that range (i.e. for one which are greater than current value).
In combination they give number of duplicates whic we deduct from rank. Result is
and in attached file
Oh my god it worked. Thank you so much