Rank based on 2 categories

  • Thread starter Thread starter Horatio J. Bilge, Jr.
  • Start date Start date
H

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
 
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.
 
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
 
Try this:

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

Copy down as needed.
 
Back
Top