Breaking tie breakers using multiple colmns

[barber.gary]

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

 

[T. Valko]

Try this:

=RANK(B2,B$2:B$6)+SUMPRODUCT(--(B2=B$2:B$6),--(C2>C$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

 

[barber.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

 

[T. Valko]

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),--(D2>D$2$6))+SUMPRODUCT(--(C2=C$2:C$6),--(D2=D$2$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:

B2>B$2:B$6

Biff

 

[barber.gary]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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

 

[barber.gary]

One final question, I think.

All of the formulas given have worked perfectly
and I was hoping to modify them to figure out how to determine points
for
each class and then the overall points when grouping both classes
together.

I have made it work with individual classes (kind of ugly formulas
though)
but I am having trouble getting the points for the overalls as you can
see by the
results of row "H" in the list below.

I am wondering if you or anyone can create a formula for the class
points and
the overall points.

Points are just the nuber of entries in the class. So if there are 6
people.
First place gets 6 points and sixth place would have 1 point. If there
are
ties (this is the part I am stuggling most with) then the points are
split
between as many people as would be needed. You can see this in the
class points column(G).

A B C D E F G H
1 Group 1
2 Name Order Distance Time Place Points OA_Pos OA_Pts
3 Name1 1 100 1 1 5.5 1 13
4 Name2 3 100 2 3 4 3 1
5 Name3 4 100 3 4 3 4 1
6 Name4 5 100 4 5 2 5 1
7 Name5 2 100 5 6 1 6 1
8 Name6 6 100 1 1 5.5 1 2
9
10 Group 2
11 Name Order Distance Time Place Points OA_Pos OA_Pts
12 Name1 7 100 15 8 1 14 1
13 Name2 1 100 8 1 8 7 1
14 Name3 2 100 9 2 7 8 1
15 Name4 3 100 10 3 6 9 1
16 Name5 4 100 11 4 4.5 10 2
17 Name6 5 100 12 6 3 12 1
18 Name7 6 100 11 4 4.5 10 2
19 Name8 8 100 14 7 2 13 1


Thank you in advance.
-Gary

 

[T. Valko]

I'm not following you on this. Take the 1st group as an example. If 1st
place is worth 6 pts then why do the first place finishers only get 5.5 pts?

Biff


One final question, I think.

All of the formulas given have worked perfectly
and I was hoping to modify them to figure out how to determine points
for
each class and then the overall points when grouping both classes
together.

I have made it work with individual classes (kind of ugly formulas
though)
but I am having trouble getting the points for the overalls as you can
see by the
results of row "H" in the list below.

I am wondering if you or anyone can create a formula for the class
points and
the overall points.

Points are just the nuber of entries in the class. So if there are 6
people.
First place gets 6 points and sixth place would have 1 point. If there
are
ties (this is the part I am stuggling most with) then the points are
split
between as many people as would be needed. You can see this in the
class points column(G).

A B C D E F G H
1 Group 1
2 Name Order Distance Time Place Points OA_Pos OA_Pts
3 Name1 1 100 1 1 5.5 1 13
4 Name2 3 100 2 3 4 3 1
5 Name3 4 100 3 4 3 4 1
6 Name4 5 100 4 5 2 5 1
7 Name5 2 100 5 6 1 6 1
8 Name6 6 100 1 1 5.5 1 2
9
10 Group 2
11 Name Order Distance Time Place Points OA_Pos OA_Pts
12 Name1 7 100 15 8 1 14 1
13 Name2 1 100 8 1 8 7 1
14 Name3 2 100 9 2 7 8 1
15 Name4 3 100 10 3 6 9 1
16 Name5 4 100 11 4 4.5 10 2
17 Name6 5 100 12 6 3 12 1
18 Name7 6 100 11 4 4.5 10 2
19 Name8 8 100 14 7 2 13 1


Thank you in advance.
-Gary

 

[David Biddulph]

I assume his logic is that they are sharing 1st and 2nd places. 6 pts for
1st, 5 pts for 2nd, so if the two tie, they each get 5.5 ?

 

[barber.gary]

David is correct. They would split the points and there would be no
tie breaker for points.
I know it may sound confusing, but even within each class the tie
breaker code you helped earlier was to determine who must go first in
the next event not to assign points. The points would be shared like
David said.

An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,

1st through fourth would each get 2.5 points (1+2+3+4)/4 and the 5th
person would get 1 point.

for a total of 15 points (1+2+3+4+5)

Please let me know if you would like more details.

Thank you very much.
-Gary

 

[barber.gary]

Boy, I "REALLY" messed up the previous post. I was looking at a sample
I made but did not realize I actually had the 5th person getting less
time than the first 4 which meant he got first and it changed the
values I was teying to give you. I really apologize for that if you
have spent time working on this.

It should be:

An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,

1st through fourth would each get 3.5 points (5+4+3+2)/4 and the 5th
person would get 1 point for a total of 15 points (5+4+3+2+1).

I am so sorry about messing up the scores. I know it takes alot to
help people and it is frustrating when you get the incorrect
information to put it together.

Thank you,
Gary



Please let me know if you would like more details.

 

[T. Valko]

Ok, I understand the logic. That's the same method used for paying out prize
money in golf tournaments.

Let me see if I can come up with something.

Biff

 

[T. Valko]

Maybe not what you had in mind but this is about the only thing I can come
up with.

Create a 2 column table:

...........J..........K
3.......1..........6
4.......2..........5
5.......3..........4
6.......4..........3
7.......5..........2
8.......6..........1

For the points, entered in F3 and copied down:

=IF(COUNTIF(E$3:E$8,E3)>1,AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTIF(E$3:E$8,E3))),INDEX(K$3:K$8,E3))

I'm assuming that "Place" emulates the RANK function.

Biff

Ok, I understand the logic. That's the same method used for paying out
prize money in golf tournaments.

Let me see if I can come up with something.

Biff

Boy, I "REALLY" messed up the previous post. I was looking at a sample
I made but did not realize I actually had the 5th person getting less
time than the first 4 which meant he got first and it changed the
values I was teying to give you. I really apologize for that if you
have spent time working on this.

It should be:

An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,

1st through fourth would each get 3.5 points (5+4+3+2)/4 and the 5th
person would get 1 point for a total of 15 points (5+4+3+2+1).

I am so sorry about messing up the scores. I know it takes alot to
help people and it is frustrating when you get the incorrect
information to put it together.

Thank you,
Gary



Please let me know if you would like more details.

David is correct. They would split the points and there would be no
tie breaker for points.
I know it may sound confusing, but even within each class the tie
breaker code you helped earlier was to determine who must go first in
the next event not to assign points. The points would be shared like
David said.

An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,

1st through fourth would each get 2.5 points (1+2+3+4)/4 and the 5th
person would get 1 point.

for a total of 15 points (1+2+3+4+5)

Please let me know if you would like more details.

Thank you very much.
-Gary

On Mar 20, 3:16 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:



I assume his logic is that they are sharing 1st and 2nd places. 6 pts
for
1st, 5 pts for 2nd, so if the two tie, they each get 5.5 ?
--
David Biddulph



I'm not following you on this. Take the 1st group as an example. If
1st
place is worth 6 pts then why do the first place finishers only get
5.5
pts?

Biff

One final question, I think.

All of the formulas given have worked perfectly
and I was hoping to modify them to figure out how to determine
points
for
each class and then the overall points when grouping both classes
together.

I have made it work with individual classes (kind of ugly formulas
though)
but I am having trouble getting the points for the overalls as you
can
see by the
results of row "H" in the list below.

I am wondering if you or anyone can create a formula for the class
points and
the overall points.

Points are just the nuber of entries in the class. So if there are 6
people.
First place gets 6 points and sixth place would have 1 point. If
there
are
ties (this is the part I am stuggling most with) then the points are
split
between as many people as would be needed. You can see this in the
class points column(G).

A B C D E F G H
1 Group 1
2 Name Order Distance Time Place Points OA_Pos OA_Pts
3 Name1 1 100 1 1 5.5 1 13
4 Name2 3 100 2 3 4 3 1
5 Name3 4 100 3 4 3 4 1
6 Name4 5 100 4 5 2 5 1
7 Name5 2 100 5 6 1 6 1
8 Name6 6 100 1 1 5.5 1 2
9
10 Group 2
11 Name Order Distance Time Place Points OA_Pos OA_Pts
12 Name1 7 100 15 8 1 14 1
13 Name2 1 100 8 1 8 7 1
14 Name3 2 100 9 2 7 8 1
15 Name4 3 100 10 3 6 9 1
16 Name5 4 100 11 4 4.5 10 2
17 Name6 5 100 12 6 3 12 1
18 Name7 6 100 11 4 4.5 10 2
19 Name8 8 100 14 7 2 13 1

Thank you in advance.
-Gary- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[T. Valko]

That formula can be reduced to:

=AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTIF(E$3:E$8,E3)))

Biff

Maybe not what you had in mind but this is about the only thing I can come
up with.

Create a 2 column table:

..........J..........K
3.......1..........6
4.......2..........5
5.......3..........4
6.......4..........3
7.......5..........2
8.......6..........1

For the points, entered in F3 and copied down:

=IF(COUNTIF(E$3:E$8,E3)>1,AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTIF(E$3:E$8,E3))),INDEX(K$3:K$8,E3))

I'm assuming that "Place" emulates the RANK function.

Biff

Ok, I understand the logic. That's the same method used for paying out
prize money in golf tournaments.

Let me see if I can come up with something.

Biff

Boy, I "REALLY" messed up the previous post. I was looking at a sample
I made but did not realize I actually had the 5th person getting less
time than the first 4 which meant he got first and it changed the
values I was teying to give you. I really apologize for that if you
have spent time working on this.

It should be:

An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,

1st through fourth would each get 3.5 points (5+4+3+2)/4 and the 5th
person would get 1 point for a total of 15 points (5+4+3+2+1).

I am so sorry about messing up the scores. I know it takes alot to
help people and it is frustrating when you get the incorrect
information to put it together.

Thank you,
Gary



Please let me know if you would like more details.






On Mar 20, 5:43 pm, (e-mail address removed) wrote:
David is correct. They would split the points and there would be no
tie breaker for points.
I know it may sound confusing, but even within each class the tie
breaker code you helped earlier was to determine who must go first in
the next event not to assign points. The points would be shared like
David said.

An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,

1st through fourth would each get 2.5 points (1+2+3+4)/4 and the 5th
person would get 1 point.

for a total of 15 points (1+2+3+4+5)

Please let me know if you would like more details.

Thank you very much.
-Gary

On Mar 20, 3:16 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:



I assume his logic is that they are sharing 1st and 2nd places. 6
pts for
1st, 5 pts for 2nd, so if the two tie, they each get 5.5 ?
--
David Biddulph



I'm not following you on this. Take the 1st group as an example. If
1st
place is worth 6 pts then why do the first place finishers only get
5.5
pts?

Biff

One final question, I think.

All of the formulas given have worked perfectly
and I was hoping to modify them to figure out how to determine
points
for
each class and then the overall points when grouping both classes
together.

I have made it work with individual classes (kind of ugly formulas
though)
but I am having trouble getting the points for the overalls as you
can
see by the
results of row "H" in the list below.

I am wondering if you or anyone can create a formula for the class
points and
the overall points.

Points are just the nuber of entries in the class. So if there are
6
people.
First place gets 6 points and sixth place would have 1 point. If
there
are
ties (this is the part I am stuggling most with) then the points
are
split
between as many people as would be needed. You can see this in the
class points column(G).

A B C D E F G H
1 Group 1
2 Name Order Distance Time Place Points OA_Pos OA_Pts
3 Name1 1 100 1 1 5.5 1 13
4 Name2 3 100 2 3 4 3 1
5 Name3 4 100 3 4 3 4 1
6 Name4 5 100 4 5 2 5 1
7 Name5 2 100 5 6 1 6 1
8 Name6 6 100 1 1 5.5 1 2
9
10 Group 2
11 Name Order Distance Time Place Points OA_Pos OA_Pts
12 Name1 7 100 15 8 1 14 1
13 Name2 1 100 8 1 8 7 1
14 Name3 2 100 9 2 7 8 1
15 Name4 3 100 10 3 6 9 1
16 Name5 4 100 11 4 4.5 10 2
17 Name6 5 100 12 6 3 12 1
18 Name7 6 100 11 4 4.5 10 2
19 Name8 8 100 14 7 2 13 1

Thank you in advance.
-Gary- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[barber.gary]

Biff,

Thank you very much, it all worked great and the worksheet is
completed. I really do appreciate all the time you spent to help me.

Gary

That formula can be reduced to:

=AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTIF(E$3:E$8,E3)))

Biff

Maybe not what you had in mind but this is about the only thing I can come
up with.

Create a 2 column table:

..........J..........K
3.......1..........6
4.......2..........5
5.......3..........4
6.......4..........3
7.......5..........2
8.......6..........1

For the points, entered in F3 and copied down:

=IF(COUNTIF(E$3:E$8,E3)>1,AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTI­F(E$3:E$8,E3))),INDEX(K$3:K$8,E3))

I'm assuming that "Place" emulates the RANK function.

Biff

Ok, I understand the logic. That's the same method used for paying out
prize money in golf tournaments.
Let me see if I can come up with something.
Biff
Boy, I "REALLY" messed up the previous post. I was looking at a sample
I made but did not realize I actually had the 5th person getting less
time than the first 4 which meant he got first and it changed the
values I was teying to give you. I really apologize for that if you
have spent time working on this.
It should be:
An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,
1st through fourth would each get 3.5 points (5+4+3+2)/4 and the 5th
person would get 1 point for a total of 15 points (5+4+3+2+1).
I am so sorry about messing up the scores. I know it takes alot to
help people and it is frustrating when you get the incorrect
information to put it together.
Thank you,
Gary
Please let me know if you would like more details.
On Mar 20, 5:43 pm, (e-mail address removed) wrote:
David is correct. They would split the points and there would be no
tie breaker for points.
I know it may sound confusing, but even within each class the tie
breaker code you helped earlier was to determine who must go first in
the next event not to assign points. The points would be shared like
David said.
An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,
1st through fourth would each get 2.5 points (1+2+3+4)/4 and the 5th
person would get 1 point.
for a total of 15 points (1+2+3+4+5)
Please let me know if you would like more details.
Thank you very much.
-Gary
On Mar 20, 3:16 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:
I assume his logic is that they are sharing 1st and 2nd places. 6
pts for
1st, 5 pts for 2nd, so if the two tie, they each get 5.5 ?
--
David Biddulph

I'm not following you on this. Take the 1st group as an example.If
1st
place is worth 6 pts then why do the first place finishers only get
5.5
pts?
Biff

One final question, I think.
All of the formulas given have worked perfectly
and I was hoping to modify them to figure out how to determine
points
for
each class and then the overall points when grouping both classes
together.
I have made it work with individual classes (kind of ugly formulas
though)
but I am having trouble getting the points for the overalls as you
can
see by the
results of row "H" in the list below.
I am wondering if you or anyone can create a formula for the class
points and
the overall points.
Points are just the nuber of entries in the class. So if there are
6
people.
First place gets 6 points and sixth place would have 1 point. If
there
are
ties (this is the part I am stuggling most with) then the points
are
split
between as many people as would be needed. You can see this in the
class points column(G).
A B C D E F G H
1 Group 1
2 Name Order Distance Time Place Points OA_Pos OA_Pts
3 Name1 1 100 1 1 5.5 1 13
4 Name2 3 100 2 3 4 3 1
5 Name3 4 100 3 4 3 4 1
6 Name4 5 100 4 5 2 5 1
7 Name5 2 100 5 6 1 6 1
8 Name6 6 100 1 1 5.5 1 2
9
10 Group 2
11 Name Order Distance Time Place Points OA_Pos OA_Pts
12 Name1 7 100 15 8 1 14 1
13 Name2 1 100 8 1 8 7 1
14 Name3 2 100 9 2 7 8 1
15 Name4 3 100 10 3 6 9 1
16 Name5 4 100 11 4 4.5 10 2
17 Name6 5 100 12 6 3 12 1
18 Name7 6 100 11 4 4.5 10 2
19 Name8 8 100 14 7 2 13 1
Thank you in advance.
-Gary- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[T. Valko]

You're welcome.

Biff

Biff,

Thank you very much, it all worked great and the worksheet is
completed. I really do appreciate all the time you spent to help me.

Gary

That formula can be reduced to:

=AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTIF(E$3:E$8,E3)))

Biff

Maybe not what you had in mind but this is about the only thing I can
come
up with.

Create a 2 column table:

..........J..........K
3.......1..........6
4.......2..........5
5.......3..........4
6.......4..........3
7.......5..........2
8.......6..........1

For the points, entered in F3 and copied down:

=IF(COUNTIF(E$3:E$8,E3)>1,AVERAGE(OFFSET(J$3,MATCH(E3,J$3:J$8,0)-1,1,COUNTI­F(E$3:E$8,E3))),INDEX(K$3:K$8,E3))

I'm assuming that "Place" emulates the RANK function.

Biff

Ok, I understand the logic. That's the same method used for paying out
prize money in golf tournaments.
Let me see if I can come up with something.
Biff
Boy, I "REALLY" messed up the previous post. I was looking at a sample
I made but did not realize I actually had the 5th person getting less
time than the first 4 which meant he got first and it changed the
values I was teying to give you. I really apologize for that if you
have spent time working on this.
It should be:
An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,
1st through fourth would each get 3.5 points (5+4+3+2)/4 and the 5th
person would get 1 point for a total of 15 points (5+4+3+2+1).
I am so sorry about messing up the scores. I know it takes alot to
help people and it is frustrating when you get the incorrect
information to put it together.
Thank you,
Gary
Please let me know if you would like more details.
On Mar 20, 5:43 pm, (e-mail address removed) wrote:
David is correct. They would split the points and there would be no
tie breaker for points.
I know it may sound confusing, but even within each class the tie
breaker code you helped earlier was to determine who must go first in
the next event not to assign points. The points would be shared like
David said.
An example would be if 4 people get the same distance and their time
was the same say 1st through 4th tied and there were 5 entrants,
1st through fourth would each get 2.5 points (1+2+3+4)/4 and the 5th
person would get 1 point.
for a total of 15 points (1+2+3+4+5)
Please let me know if you would like more details.
Thank you very much.
-Gary
On Mar 20, 3:16 pm, "David Biddulph" <groups [at] biddulph.org.uk>
wrote:
I assume his logic is that they are sharing 1st and 2nd places. 6
pts for
1st, 5 pts for 2nd, so if the two tie, they each get 5.5 ?
--
David Biddulph

I'm not following you on this. Take the 1st group as an example.
If
1st
place is worth 6 pts then why do the first place finishers only
get
5.5
pts?
Biff

One final question, I think.
All of the formulas given have worked perfectly
and I was hoping to modify them to figure out how to determine
points
for
each class and then the overall points when grouping both classes
together.
I have made it work with individual classes (kind of ugly
formulas
though)
but I am having trouble getting the points for the overalls as
you
can
see by the
results of row "H" in the list below.
I am wondering if you or anyone can create a formula for the
class
points and
the overall points.
Points are just the nuber of entries in the class. So if there
are
6
people.
First place gets 6 points and sixth place would have 1 point. If
there
are
ties (this is the part I am stuggling most with) then the points
are
split
between as many people as would be needed. You can see this in
the
class points column(G).
A B C D E F G H
1 Group 1
2 Name Order Distance Time Place Points OA_Pos OA_Pts
3 Name1 1 100 1 1 5.5 1 13
4 Name2 3 100 2 3 4 3 1
5 Name3 4 100 3 4 3 4 1
6 Name4 5 100 4 5 2 5 1
7 Name5 2 100 5 6 1 6 1
8 Name6 6 100 1 1 5.5 1 2
9
10 Group 2
11 Name Order Distance Time Place Points OA_Pos OA_Pts
12 Name1 7 100 15 8 1 14 1
13 Name2 1 100 8 1 8 7 1
14 Name3 2 100 9 2 7 8 1
15 Name4 3 100 10 3 6 9 1
16 Name5 4 100 11 4 4.5 10 2
17 Name6 5 100 12 6 3 12 1
18 Name7 6 100 11 4 4.5 10 2
19 Name8 8 100 14 7 2 13 1
Thank you in advance.
-Gary- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -