Rank based on 2 categories
 

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 a
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

Rank based on 2 categories
 

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.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
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 a
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

Rank based on 2 categories
 

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
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 a
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

Rank based on 2 categories
 

Hello,

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

Regards,
Bernd

Rank based on 2 categories
 

Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote
in
message ...
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 a
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

Rank based on 2 categories
 

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" wrote:

Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote
in
message ...
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 a
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

Rank based on 2 categories
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote in
message ...
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" wrote:

Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr." wrote
in
message ...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Horatio J. Bilge, Jr."
wrote
in
message ...
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 a
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