MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to do a ranking


Posted by Kiko on January 09, 2002 6:16 AM

I have five people playing a game (players A, B, C, D and E). At the end of the game, I input in a spreadsheet the amounts each player has earned during the game. So, let's say:
A= 60.00
B= 20.00
C= -10.00
D= -40.00
E= -30.00

What I want to do is to attribute points to the players the following points, as in a ranking, according to the money earned by each one (the first player being the one that earned more money, the last one being the one who earned less money):

First (more money) = 10 points
Second = 6 points
Third = 4 points
Fourth = 3 points
Fifth (less money) = 2 points

How do I do that?

Oh, there is a catch: if, let's say, there is a draw between two players, than they would receive the same points attributed to the position in the ranking (so, if there is a draw in the second position, both player would receive 6 points); HOWEVER, than there would be no third position, the ranking would skip the third position and the next player in the ranking would be ranked in the fourth position. The same if there is a draw among more than two players: if, let's say, three players are tied in the first position, than the next player would be ranked in the fourth position.

Any ideas?

Regards,

Fabricio


Posted by Aladin Akyurek on January 09, 2002 6:32 AM

Fabricio,

It seems you're looking for:

=VLOOKUP(RANK(B2,$B$2:$B$6),{1,10;2,6;3,4;4,3;5,2},2,0)

to be entered in C2. I assumed your sample data to be in A2:B6.

Aladin

========

Posted by Rob Jackson on January 09, 2002 6:40 AM

OK, this is the way I would do it (without using macro's)
Create a small table somewhere out of sight on the sheet. Have two columns of five cells. Ranking score and Allocated value. Row 1 should be the top rank down to row 5 the bottom rank.
Use the formula Large(<range>,<rank>) where range is the range of cells where you input the scores and <rank> 1 to 5 according to row. This will complete your Ranking Score Value. (I have tried to show this bellow but the columns dont line up correctly)

Rank Ranking Score Allocation
Rank 1 60 10
Rank 2 40 6
Rank 3 30 4
Rank 4 30 3
Rank 5 2 2


Your Input Table will look like this:

Player Score Allocation
Player1 60 10
Player2 30 4
Player3 30 4
Player4 40 6
Player5 2 2

The Allocation Column is completed by match and offset or Vlookup on the smaller table to match the score against the allocation.

I hope this helps.

Rob

Posted by Rob Jackson on January 09, 2002 6:45 AM

Aladin
Nice answer,
I didn't know you could do that with Vlookup.

Rob

Posted by Fabricio on January 09, 2002 12:04 PM

Thanks, Aladin! It worked just fine!