Need Help on Ranking

[computerteckie]

I am looking for a formula to rank the position of a person based o
tasks completed.

Conditions:
The total number of tasks is the same for all (18)
More then one person can have the same amount of tasks done placin
them in the same ranking.
Ranking Must be in a linier progression without skipping ANY number
(RANK 1,2,3,4,5 etc…)

The end result will need to look something like this

TOTAL TASKS: 18

NAME----TASKS DONE---------RANK
John------------4--------------------3
Bill---------------2-------------------5
Karen-----------4-------------------3
Jim---------------3-------------------4
Sally-------------6-------------------2
Jill----------------2-------------------5
Harry------------8-------------------1
Peter------------8-------------------1

This means duplicates need to be considered and the spreadsheet canno
jump from Rank 1 to Rank 3 ignoring rank 2 (the RANK function will d
this).

Your suggestions are appreciated

 

[jeff]

Hi,

Use the Rank function. However, you'll have to devise
what criteria to use (and implement it) in case of
ties.

jeff

 

[Aladin Akyurek]

Let A1:B9 house the sample you provided:

{"NAME","TASKS
DONE";"John",4;"Bill",2;"Karen",4;"Jim",3;"Sally",6;"Jill",2;"Harry",8;"Pete
r",8}

In C2 enter & copy down:

=IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),"",RANK(B2,$B$2:$B$9))

In D2 enter & copy down:

=IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),VLOOKUP(B2,$B$11,3,0),RANK(C2,$C$2:$C$9,
1))

Column D now houses the desired ranking. C19 would like this:

{"I-RANK","RANK";
4,3;
7,5;
"",3;
6,4;
3,2;
"",5;
1,1;
"",1}

 

[computerteckie]

Works Great, Thank you. Now if I can just put it all in one formula i
would be perfect.

I got this far

=IF(ISNUMBER(MATCH(U3,$U$2:U2,0)),VLOOKUP(U3,$U$2:W2,3,0),RANK(IF(ISNUMBER(MATCH(U3,$U$2:U2,0)),"",RANK(U3,$U$3:$U$65)),$V$3:$V$65,1))

(had to modify the cell numbers due to actual placement as the dat
being ranked starts in cell U3 and ends in cell U65)

Thank

 

[Aladin Akyurek]

Works Great, Thank you.

You're welcome.

Now if I can just put it all in one formula it
would be perfect.

You can't. The final round of ranking needs the results of the first round
of ranking.

[...]

 

[computerteckie]

Ill cheat then. (move column and make the text white