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