RANK - Remove repeated ranks for sorting

[BlueWolverine]

Hello,
MS EXCEL 2003 on XP PRO.

for the overall simplicity of my spreadsheet, I want to use a rank as a
unique identifier so I use VLOOKUPS. My problem is that RANK() is VERY
likely to return repeated ranks (3 second places for instance) and thus mess
up my whole day.

I found that the correction offered in F1 Help
=(COUNT(AB:AB) + 1 - RANK($AB4,$AB:$AB,0)- RANK($AB4,$AB:$AB,1))/2
to produce the same effect.

Does anyone have a recommendation on how to rank say,

{1,2,2,2,3,3,4,5,6,7} with out repeating the rank? (largest is Rank = 1)?
{10,9,8,7,6,5,4,3,2,1} are the ranks for each number in the set above
Thank you!

 

[Teethless mama]

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

 

[Bob Umlas]

If the #s are in A2:A11, enter this in B2:
=RANK(A2,$A$2:$A$11,0)
and ARRAY-ENTER this in C2:
=SUM((A2<$A$2:$A$11)*1)+1+COUNTIF($B$1:B1,B2)
then fill B2:C2 down to C11. Your answers are in C2:C11.
HTH

 

[BlueWolverine]

=RANK($AB3,$AB:$AB,0)+IF(COUNTIF($AB:$AB,$AB3)>1,A3/1000000,0)

This works better, then format as number with zero decimal places.

column A is just 1 2 3 4 5 6 7 8 9 etc.

 

[Glenn]

Hello,
MS EXCEL 2003 on XP PRO.

for the overall simplicity of my spreadsheet, I want to use a rank as a
unique identifier so I use VLOOKUPS. My problem is that RANK() is VERY
likely to return repeated ranks (3 second places for instance) and thus mess
up my whole day.

I found that the correction offered in F1 Help
=(COUNT(AB:AB) + 1 - RANK($AB4,$AB:$AB,0)- RANK($AB4,$AB:$AB,1))/2
to produce the same effect.

Does anyone have a recommendation on how to rank say,

{1,2,2,2,3,3,4,5,6,7} with out repeating the rank? (largest is Rank = 1)?
{10,9,8,7,6,5,4,3,2,1} are the ranks for each number in the set above
Thank you!

With your data in A2:A11, put this in B2 and copy down to B11:

=COUNTIF($A$2:$A$11,">="&A2)-COUNTIF($A$2:A2,A2)+1

 

[Glenn]

With your data in A2:A11, put this in B2 and copy down to B11:

=COUNTIF($A$2:$A$11,">="&A2)-COUNTIF($A$2:A2,A2)+1

Inverse ranking {1,2,3, ...} would be this:

=COUNTIF($A$2:$A$11,"<="&A2)-COUNTIF(A2:$A$11,A2)+1

 

[Bernd P]

Hello,

I suggest to use Countif instead of RankÖ
http://sulprobil.com/html/rank.html

Regards,
Bernd

 

[Glenn]

I got {10,7,8,9,5,6,4,3,2,1} with your formulas.

If the #s are in A2:A11, enter this in B2:
=RANK(A2,$A$2:$A$11,0)
and ARRAY-ENTER this in C2:
=SUM((A2<$A$2:$A$11)*1)+1+COUNTIF($B$1:B1,B2)
then fill B2:C2 down to C11. Your answers are in C2:C11.
HTH

 

[Glenn]

I got {10,7,8,9,5,6,4,3,2,1} with your formulas.

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

 

[Glenn]

Hello,

I suggest to use Countif instead of RankÖ
http://sulprobil.com/html/rank.html

Regards,
Bernd

Also yields close (but not exact) results as requested by the OP.

Try this:

=COUNTIF($A$4:$A$8,">="&A4)-COUNTIF($A$4:A4,A4)+1

instead of this:

=COUNTIF($A$4:$A$8,">"&A4)+COUNTIF($A$4:A4,A4)