0

I am trying to make a list of top 25 based on the rank function in excel, but my list is missing the 11th and 20th value. Upon inspection, I noticed that there are identical values in these spots preventing them from ranking correctly. Is there a way to give each a unique rank? (i.e. John and Sam both have the value 31 and their ranks should be 10th and 11th instead of both being 10th.)

Matt C.
  • 1
  • 1
  • 1

2 Answers2

0

Consider:

enter image description here

Both Patricia and Mary have the same score, but they need different ranks. In C2 enter:

=RANK(A2,$A$2:$A$11,0)+COUNTIF($A$2:$A2,A2)-1

and copy down:

enter image description here

This makes ranks that are simple, sequential with no gaps and no dups.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

Gary's answer works if you have a list of x people and want x rankings. If you had a list of x people and y rankings, you could use something like:

=(COUNTIF($B$2:B2,B2)-1)*0.0001+B2

to change the value of the thing to rank to make each person unique, seen here.

enter image description here

Then, you can put the formula 

=MATCH(LARGE(C:C,ROW()),C:C,0)

in E, copied down how many ever ranks you want and finally 

=INDIRECT("A"&E2)

in F, copied down again how many ranks you want. This returns the name of the person, ranked without duplicates and with any arbitrary number of ranks you want.

KFichter
  • 773
  • 4
  • 15