Ranking by multiple Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zaphod117 - 14 Sep 2007 05:14 GMT

I would like to rank a group based on two Criteria, and i can't figure it
out. I've got a list of 10 teams and I want to rank them based first on
winning % then by total points earned. Here is an exaple of the dataset:

A B
1 .75 256
2 .48 305
3 .53 368
4 .53 412

So in this example, 1 would be first, then 4, then 3, then 2.

I know that I can sort them and get the return manually, but I really don't
want to have to manually sort the list every day. Any bright ideas?

Reply to this Message

pdberger - 14 Sep 2007 05:30 GMT

Zaphod --

Select the whole table, then click >Data>Sort. In your example, sort first
for column A (descending) then column B (descending). It would be easier if
you organized it like this:

A B C
1 Team Percent Runs
2 A 75% 256
etc.

Then, when you do the sort, you'll see the actual column names by which to
sort.

HTH

> I would like to rank a group based on two Criteria, and i can't figure it
> out. I've got a list of 10 teams and I want to rank them based first on
[quoted text clipped - 10 lines]
> I know that I can sort them and get the return manually, but I really don't
> want to have to manually sort the list every day. Any bright ideas?

Reply to this Message

T. Valko - 14 Sep 2007 07:05 GMT

Try this:

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

Copy down

Signature

Biff
Microsoft Excel MVP

>I would like to rank a group based on two Criteria, and i can't figure it
> out. I've got a list of 10 teams and I want to rank them based first on
[quoted text clipped - 11 lines]
> don't
> want to have to manually sort the list every day. Any bright ideas?

Reply to this Message