Cross Country Tie Breaking Formula

Hello,

I'm looking for a formula that will break ties in cross country. For those that don't know, in cross country you add up the places for the top 5 runners on a team and the lowest score wins. If there is a tie, in high school you compare the places of the 6th runner and who ever finished lower, has the higher team place. In college, they break ties by comparing the places of the #1-5 Runners for the two tied teams head to head, and each head to head win gets a point. First to 3, wins the tiebreaker. Here is an example:


Team Pts R1 R2 R3 R4 R5 R[6]
Team 1 54 1 9 11 12 21
Team 2 54 2 8 9 13 22 26

In this example, Team 1 wins in College [1<2 (1pt), 12<13 (1pt), 21<22 (1pt)], but loses in High School (finishing 26th is better than no finisher).


I figured out a way to do tie breaking based on high school rules, by assigning a fractional point to the 6th runner and adding it to the point total. In this case, 26/10000 of a point. Since it will always be less than one it shouldn't mess up the ranking. If there is no 6th man, I assign 9999/10000 of a point to their total.

I'm struggling with ways to break based on college rules. I think the easiest way would be to use this same fractional point method, but I am not sure how best to be able to do a running total. In the work book attached, I did columns that will look at the respective places and if one teams runner beat the other teams runner, they would receive -0.0001 points (since lower point totals rank you higher). I also don't know how to match the tied teams automatically and be able to compare their two runners.

Formula:

Please Login or Register  to view this content.

A formula like this would be ideal, but it doesn't keep the running total.

Something like this in H2?

=D2+SUM(IF(J2:N2=BYCOL(IF($D$2:$D$35=D2,$J$2:$N$35,9999),LAMBDA(col,MIN(col))),-0.0001,0))

It might not work for a 3-way tie, but I expect that's pretty rare and I don't know what the rules are for splitting it.

Very cool. Both of these formulas worked. Can you just explain what is actually going on? The formulas work, but I'm lost on what is actually happening. How are you guys comparing the right values? I guess I'm not familiar enough with actually using the MIN functiona nd the SIGN Function!

Thank you both!

IF($D$2:$D$35=D2,$J$2:$N$35,9999)

This bit checks for ties based on column D, and returns the position of the runners for each team, giving a vlue of 9999 otherwise (because I want to find the MIN so this is just an arbitrary "higher than all the actual values" number)

For example for the first row this will return:

1 9 11 12 21
9999 9999 9999 9999 9999
9999 9999 9999 9999 9999
2 8 9 13 22
9999 9999 9999 9999 9999
9999 9999 9999 9999 9999

And then lots more rows of 9999

BYCOL lets you do something to each column in turn, so I find the MIN and then compare with the current row. If they are the same, this means that team won the head to head, so I subtract 0.0001 from their total.

The longer version uses similar logic but without using BYCOL so it does each of the 5 columns in turn.