RANK: Changing the sequens of duplicate values

[Krutte]

Hi

I have made a ranking list in descending order using the formula =RANK(A1,$A$1:$A$10,1) where 10 is the highest score and 1 is the lowest score.

The ranking list contains duplicate values, and the returned order is:

10, 8, 8, 7, 6, 5, 3, 3, 2, 1

I would like the results to be reported as

10, 9, 9, 7, 6, 5, 4, 4, 2, 1

Is this possible?

Thanks

 

[Luke M]

You'll want to subtract a descending rank from the best possible score.
=COUNT($A$1:$A$10)+1-RANK(A1,$A$1:$A$10,0)

 

[Krutte]

Thank you for your reply. Unfortunately it wasn't quite what I needed, so I am afraid I haven't expressed my self clearly:

I am going to rank sports score, so the higher the score, the higher the rank (more ranking points), and the higher the person will be on the list.

The formula I am using RANK(A1,$A$1:$A$10,1) is seemingly doing the job, so I guess it is ascending. I am just sorting the list descendingly (Z to A).

So far, so good. I would also like to keep the duplicated values, but instead of having e.g. one first place and two shared third places:

10 (1. place)
8 (3. place)
8 (3. place)
7 (4. place)
6 etc.
5
3
3
2
1

I would prefer to have one first place and two shared second places:

10 (1. place)
9 (2. place)
9 (2. place)
7 (4. place)
6 etc.
5
4
4
2
1

I hope this makes it more clear

 

[Luke M]

Hi Krutte,

I understood, but I don't think you even tried my solution. If you had, you would see:

That indeed, the highest sports score gets the most points (10), and then there is a tie for 2nd (9 pts), and so on.

 

[Krutte]

YES! HURRAY!! Of course it worked! I had forgotten the $'s prior to copying the for formula, so ended up with something almost right...

Never doubt an expert - thanks a lot :-D