I am trying to come up with a function to rank finishes and break ties
based off two columns.

An example of the worksheet would be:

A B C D
1 NAME DISTANCE TIME RANK
2 Name1 100 72 tie
3 Name2 100 65 tie
4 Name3 85 60 4
5 Name4 79 40 5
6 Name5 100 92 tie


They must cover a distance in the fastest time to win. I am having
trouble figuring the ties using column C (TIME) which should be the
lower time wins. I would like to use formulas if possible to update
the worksheet instantly.

Thanks in advance.

-Gary

Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2C$2:C$6))

This could still result in ties if both column B and C are the same:

Name1 100 72
Name4 100 72

Biff

wrote in message
oups.com...
I am trying to come up with a function to rank finishes and break ties
based off two columns.

An example of the worksheet would be:

A B C D
1 NAME DISTANCE TIME RANK
2 Name1 100 72 tie
3 Name2 100 65 tie
4 Name3 85 60 4
5 Name4 79 40 5
6 Name5 100 92 tie


They must cover a distance in the fastest time to win. I am having
trouble figuring the ties using column C (TIME) which should be the
lower time wins. I would like to use formulas if possible to update
the worksheet instantly.

Thanks in advance.

-Gary

Thank you very much T. Valko. This did work perfect, but your comment
about ties in both columns would be an issue I did not think of.

There is a third column that can be used if you or someone can make a
formula work. The competitors have a predetermined order in which they
will compete in the first event and it is in a column by itself. For
example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3 (Don't ask me
why, it is just the way the rules are). The "full" sheet is laid out
similar to this:

A B C D E
1 NAME ORDER DISTANCE TIME RANK
2 Name1 1 100 72 tie
3 Name2 2 100 72 tie
4 Name3 3 85 60 4
5 Name4 4 79 40 5
6 Name5 5 100 92 tie


Thanks in advance.
-Gary




On Mar 17, 11:09 pm, "T. Valko" wrote:
Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2C$2:C$6))

This could still result in ties if both column B and C are the same:

Name1 100 72
Name4 100 72

Biff

wrote in message

oups.com...



I am trying to come up with a function to rank finishes and break ties
based off two columns.

An example of the worksheet would be:

A B C D
1 NAME DISTANCE TIME RANK
2 Name1 100 72 tie
3 Name2 100 65 tie
4 Name3 85 60 4
5 Name4 79 40 5
6 Name5 100 92 tie

They must cover a distance in the fastest time to win. I am having
trouble figuring the ties using column C (TIME) which should be the
lower time wins. I would like to use formulas if possible to update
the worksheet instantly.

Thanks in advance.

-Gary- Hide quoted text -

- Show quoted text -

For example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3

Is that a typo?

Try this:

=RANK(C2,C$2:C$6)+SUMPRODUCT(--(C2=C$2:C$6),--(D2D$2:D$6))+SUMPRODUCT(--(C2=C$2:C$6),--(D2=D$2:D$6),--(B2<B$2:B$6))

Based on your data the ranks would be 2,1,4,5,3

If the correct ranks should be 1,2,4,5,3

Change this portion of the formula:

B2<B$2:B$6

To:

B2B$2:B$6

Biff

wrote in message
ps.com...
Thank you very much T. Valko. This did work perfect, but your comment
about ties in both columns would be an issue I did not think of.

There is a third column that can be used if you or someone can make a
formula work. The competitors have a predetermined order in which they
will compete in the first event and it is in a column by itself. For
example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3 (Don't ask me
why, it is just the way the rules are). The "full" sheet is laid out
similar to this:

A B C D E
1 NAME ORDER DISTANCE TIME RANK
2 Name1 1 100 72 tie
3 Name2 2 100 72 tie
4 Name3 3 85 60 4
5 Name4 4 79 40 5
6 Name5 5 100 92 tie


Thanks in advance.
-Gary




On Mar 17, 11:09 pm, "T. Valko" wrote:
Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2C$2:C$6))

This could still result in ties if both column B and C are the same:

Name1 100 72
Name4 100 72

Biff

wrote in message

oups.com...



I am trying to come up with a function to rank finishes and break ties
based off two columns.

An example of the worksheet would be:

A B C D
1 NAME DISTANCE TIME RANK
2 Name1 100 72 tie
3 Name2 100 65 tie
4 Name3 85 60 4
5 Name4 79 40 5
6 Name5 100 92 tie

They must cover a distance in the fastest time to win. I am having
trouble figuring the ties using column C (TIME) which should be the
lower time wins. I would like to use formulas if possible to update
the worksheet instantly.

Thanks in advance.

-Gary- Hide quoted text -

- Show quoted text -

That was less of a typo and mainly me just not paying attention to the
rows.

Thank you very much for the formula. This works perfectly.

Have a great day!!
-Gary



On Mar 19, 12:50 pm, "T. Valko" wrote:
For example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3

Is that a typo?

Try this:

=RANK(C2,C$2:C$6)+SUMPRODUCT(--(C2=C$2:C$6),--(D2D$2:D$6))+SUMPRODUCT(--(C*2=C$2:C$6),--(D2=D$2:D$6),--(B2<B$2:B$6))

Based on your data the ranks would be 2,1,4,5,3

If the correct ranks should be 1,2,4,5,3

Change this portion of the formula:

B2<B$2:B$6

To:

B2B$2:B$6

Biff

wrote in message

ps.com...



Thank you very much T. Valko. This did work perfect, but your comment
about ties in both columns would be an issue I did not think of.

There is a third column that can be used if you or someone can make a
formula work. The competitors have a predetermined order in which they
will compete in the first event and it is in a column by itself. For
example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3 (Don't ask me
why, it is just the way the rules are). The "full" sheet is laid out
similar to this:

A B C D E
1 NAME ORDER DISTANCE TIME RANK
2 Name1 1 100 72 tie
3 Name2 2 100 72 tie
4 Name3 3 85 60 4
5 Name4 4 79 40 5
6 Name5 5 100 92 tie

Thanks in advance.
-Gary

On Mar 17, 11:09 pm, "T. Valko" wrote:
Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2C$2:C$6))

This could still result in ties if both column B and C are the same:

Name1 100 72
Name4 100 72

Biff

wrote in message

groups.com...

I am trying to come up with a function to rank finishes and break ties
based off two columns.

An example of the worksheet would be:

A B C D
1 NAME DISTANCE TIME RANK
2 Name1 100 72 tie
3 Name2 100 65 tie
4 Name3 85 60 4
5 Name4 79 40 5
6 Name5 100 92 tie

They must cover a distance in the fastest time to win. I am having
trouble figuring the ties using column C (TIME) which should be the
lower time wins. I would like to use formulas if possible to update
the worksheet instantly.

Thanks in advance.

-Gary- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You're welcome. Thanks for the feedback!

Biff

wrote in message
oups.com...
That was less of a typo and mainly me just not paying attention to the
rows.

Thank you very much for the formula. This works perfectly.

Have a great day!!
-Gary



On Mar 19, 12:50 pm, "T. Valko" wrote:
For example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3

Is that a typo?

Try this:

=RANK(C2,C$2:C$6)+SUMPRODUCT(--(C2=C$2:C$6),--(D2D$2:D$6))+SUMPRODUCT(--(C*2=C$2:C$6),--(D2=D$2:D$6),--(B2<B$2:B$6))

Based on your data the ranks would be 2,1,4,5,3

If the correct ranks should be 1,2,4,5,3

Change this portion of the formula:

B2<B$2:B$6

To:

B2B$2:B$6

Biff

wrote in message

ps.com...



Thank you very much T. Valko. This did work perfect, but your comment
about ties in both columns would be an issue I did not think of.

There is a third column that can be used if you or someone can make a
formula work. The competitors have a predetermined order in which they
will compete in the first event and it is in a column by itself. For
example if competitor #1 and competitor #3 tie. Competitor #1 would
have to go first on the next event. In other words Competitor #3 would
win the tie breaker since he started after competitor #3 (Don't ask me
why, it is just the way the rules are). The "full" sheet is laid out
similar to this:

A B C D E
1 NAME ORDER DISTANCE TIME RANK
2 Name1 1 100 72 tie
3 Name2 2 100 72 tie
4 Name3 3 85 60 4
5 Name4 4 79 40 5
6 Name5 5 100 92 tie

Thanks in advance.
-Gary

On Mar 17, 11:09 pm, "T. Valko" wrote:
Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2C$2:C$6))

This could still result in ties if both column B and C are the same:

Name1 100 72
Name4 100 72

Biff

wrote in message

groups.com...

I am trying to come up with a function to rank finishes and break ties
based off two columns.

An example of the worksheet would be:

A B C D
1 NAME DISTANCE TIME RANK
2 Name1 100 72 tie
3 Name2 100 65 tie
4 Name3 85 60 4
5 Name4 79 40 5
6 Name5 100 92 tie

They must cover a distance in the fastest time to win. I am having
trouble figuring the ties using column C (TIME) which should be the
lower time wins. I would like to use formulas if possible to update
the worksheet instantly.

Thanks in advance.

-Gary- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -