I am building an excel sheet that will score cross country meets. I know there are apps and software you can buy, but I have learned a ton of excel by making it myself.
I have an excel sheet that will score 8 teams perfectly. However, I cannot figure out how to break a tie. Without going into too much detail of scoring a meet. here is what I need. See the picture for reference. Teams are ranked in column M, team names in N and scores in O. As you can see two teams have a score of 75 and are both ranked 2nd. In cross county there is no tieing. You break this tie by which teams 6th runner came in first (runners 1-5 make up your score so if you tie in score of runners 1-5 the tieing team with their 6th runner finishing first wins).
I have each teams 6th runner's place in column P (note some teams may not have a 6th runner and this should be taken into account).
Basically I need to re-rank any ties in column O based on the lowest number JUST BETWEEN THE TIEING TEAMS in column P (the 6th place runner). My problem is I cannot figure out how to write a function that only breaks the tie and reranks when there is a tie. I do not want all teams reranked based on the 6th runner.
Any help here would be gladly welcomed.
Screen Shot 2017-07-11 at 3.18.41 PM.png
Bookmarks