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:

=IF(J2<J5,D2-0.0001,and(IF(K2<K5,D2-0.0001),and(IF(L2<L5,D2-0.0001),and(IF(M2<M5,D2-0.0001),and(IF(N2<N5,D2-0.0001))))))

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