Ranking Values that Meet a Certain Criteria

[andy.hogan]

How can I rank a set of values in a range that meet a specified
criteria?

An example:
range A1:A10 contains the number of wins a team has. range B1:B10
contains each team's goal differential. For teams that have the same
number of wins, I want to be able to rank them on their goal
differential. I don't want to sort the list and then rank (this is a
simplified example) - rather having excel automatically identifying
all of the ties and breaking them.

Any help??

Thanks,
adh

 

[T. Valko]

Try this:

The higher goal differential is ranked better.

Entered in C1:

=RANK(A1,A$1:A$10)+SUMPRODUCT(--(A1=A$1:A$10),--(B1<B$1:B$10))

Copy down to C10

Biff

 

[andy.hogan]

thanks, biff.

would it be possible to add a third ranking criteria? say two teams
are tied for both wins and goals scored, so i want to break that tie
in a third column (say, C1:C10) which is goals scored at home.

thanks!!

-adh

 

[T. Valko]

The higher goals scored are ranked better:

=RANK(A1,A$1:A$10)+SUMPRODUCT(--(A1=A$1:A$10),--(B1<B$1:B$10))+SUMPRODUCT(--(A1=A$1:A$10),--(B1=B$1:B$10),--(C1<C$1:C$10))

Biff