How do I prevent tied results when ranking data?
 

When I use rank to identify the Top 50, I sometimes get a tie, I would like
to be able to always have a rank of 1,2,3,4,etc even if the standard rank
function generated 1,2,2,4 (i.e. although the data has actually got 2 equal
values, I would like to distinguish them, by having one ranked at 2 and one
ranked at 3.

Hi
see:
http://www.xldynamic.com/source/xld.RANK.html
and
http://www.cpearson.com/excel/rank.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"laidbackgraham" schrieb im
Newsbeitrag ...
When I use rank to identify the Top 50, I sometimes get a tie, I
would like
to be able to always have a rank of 1,2,3,4,etc even if the standard
rank
function generated 1,2,2,4 (i.e. although the data has actually got 2
equal
values, I would like to distinguish them, by having one ranked at 2
and one
ranked at 3.

Frank Kabel wrote...
see:
http://www.xldynamic.com/source/xld.RANK.html

How is this relevent to the OP's question? It's a long and overly
pedantic exposition of adding ordinal suffixes to cardinal ranks. And
it concludes with the redundant formula

=CHOOSE(AND(x<{11,12,13})*MIN(4,MOD(x,10))+1,"th" ,"st","nd","rd","th")

rather than the shorter

=CHOOSE(MIN(3,MOD((ABS(x-12)1)*x-1,10))+1,"st","nd","rd","th")

and
http://www.cpearson.com/excel/rank.htm

More to the point.


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=274557

Assuming that Column A contains your values...

B1, copied down:

=RANK(A1,$A$1:$A$10)+COUNTIF($A$1:A1,A1)-1

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=274557

laidbackgraham Wrote:
When I use rank to identify the Top 50, I sometimes get a tie, I would
like
to be able to always have a rank of 1,2,3,4,etc even if the standard
rank
function generated 1,2,2,4 (i.e. although the data has actually got 2
equal
values, I would like to distinguish them, by having one ranked at 2 and
one
ranked at 3.

Looks like you're constructing a Top 50 list. That also requires making
a distinction between ties. Here is an example of constructing a Top N
list:

http://tinyurl.com/22x6k


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274557