Rank based on 2 categories

[Horatio J. Bilge, Jr.]

I was using RANK() to assign places based on a score, but now I have to
separate the places by team. I found a post suggesting an array formula, but
there are some problems with it. In column A (named "Team") is the team name;
column B (named "Score") is the score upon which I want to rank, and columns
C, D, and E are the columns where I want the rank to appear. I'll post sample
data below.

Here is the formula in column C (for Team A):
{=IF(A2<>"Team
A","",COUNTIF(Team,A2)+1-MATCH(TRUE,B2=LARGE(Score*(A2=Team),ROW(INDIRECT("1:"&COUNTIF(Team,A2)))),0))}

For columns D and E, "Team A" is changed to "Team B" and "Team C"

The problems are:
1. When the score column is empty, the formula returns the number of people
on that team, instead of blank.
2. It ranks scores in reverse order (highest score should be #1, but this
formula makes the lowest score #1).
3. When there is a tie, it gives both people the lower place (for Team B
below, the ranks should be 2, 2, 1, instead of 3, 3, 1 (and if ranked in the
correct order, it would be 1, 1, 3)).

Team Score Rank A Rank B Rank C
Team A 5 3
Team A 4 2
Team A 3 1
Team B 4 3
Team B 4 3
Team B 1 1
Team C 5 3
Team C 3 2
Team C 3 2

 

[T. Valko]

Based on the sample data you posted, show us what ranks you expect and
include an empty score cell or 2 so we can see how the empty score cells
affect the expected ranks.

 

[Horatio J. Bilge, Jr.]

Here is the same data, sorted by score, so the teams are mixed up. The Rank
columns show the expected result. I left the score for one of the Team C
people blank.

Team Score Rank A Rank B Rank C
Team A 5 1
Team C 5 1
Team A 4 2
Team B 4 1
Team B 4 1
Team A 3 3
Team C
Team C 3 2
Team B 1 3

 

[Bernd P]

Hello,

I suggest to look here:
http://sulprobil.com/html/sorting.html

Regards,
Bernd

 

[T. Valko]

Try this:

=IF(B2="","",SUMPRODUCT(--(A$2:A$10=A2),--(B2<B$2:B$10))+1)

Copy down as needed.

 

[Horatio J. Bilge, Jr.]

That seems to work well with my sample data. I'll try it tomorrow in the
actual workbook.
Now I need to figure out what your formula is actually doing... I found a
webpage that seems to explain SUMPRODUCT pretty thoroughly, so I will be
doing some reading.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks!
~ Horatio

 

[T. Valko]

You're welcome. Thanks for the feedback!