How do I prevent tied results when ranking data?

[Guest]

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]

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

 

[hgrove]

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 overl
pedantic exposition of adding ordinal suffixes to cardinal ranks. An
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

 

[Domenic]

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

 

[Aladin Akyurek]

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

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

http://tinyurl.com/22x6