Complex tiebreaker

[HorizonHunter]

Building a an F1 scenario tool that includes driver rankings across the course of a season.
Scoring model gives points to the top 10 performances at race. RANK.EQ based on points works great for those 10. However every driver after that is ranked 11 after the first race.
The way tie breaking works, looks at the x number of drivers that have a points tie, who has the most first place finishes? Tie? Who has the most second place finishes? Tie? Third place, and so on.

My sheet is set up with drivers on rows, with iterated columns sections for each race that include race finish position, and cumulative points accrued in the season.

I've created 23 additional charts below for each race that tallies each drivers total appearances in each finishing place.
Ex, Verstappen and Perez both have 1 1st place and 1 2nd place after 2 rounds, and Alonso has 2 3rd place finishes.

How do I create a tie breaking formula to follow after RANK.EQ for each round of the season (1-23) that references the corresponding placement chart for that round?

 

[jimrward]

The method I use for breaking complex ties is as follows
Create a helper column and the contents will be
Points + (criteria1/10) + (criteria2/100) + (criteria3/1000)
This will give you a decimal number to rank on
You will have to fill in the criteriaX bit with lookups or index/match to get the 1st place, 2nd place counts etc

 

[HorizonHunter]

The method I use for breaking complex ties is as follows
Create a helper column and the contents will be
Points + (criteria1/10) + (criteria2/100) + (criteria3/1000)
This will give you a decimal number to rank on
You will have to fill in the criteriaX bit with lookups or index/match to get the 1st place, 2nd place counts etc

Thanks. I'm going to try this later. I built formula with RANK.EQ followed by a long string of 20 COUNTIFS, but ran in to the logic issue that the calculations kept running, even after the tie was broken.

 

[jimrward]

Possibly you only need a handful of tie breakers should resolve most ties

 

[HorizonHunter]

The method I use for breaking complex ties is as follows
Create a helper column and the contents will be
Points + (criteria1/10) + (criteria2/100) + (criteria3/1000)
This will give you a decimal number to rank on
You will have to fill in the criteriaX bit with lookups or index/match to get the 1st place, 2nd place counts etc

This worked perfectly, thank you! I was able to skip the helper column and index/match by building it in to the existing total points column. Did end up being a long string to incorporate tie-breaker data for all 20 positions. 21 zeroes on the last criteria check.

 

[jimrward]

in a real life scenario i doubt you will need more than a handfull of tie breaks, the more you have you will be getting near to excels accuracy for decimal places
can you post your large formula just for reference

 

[HorizonHunter]

in a real life scenario i doubt you will need more than a handfull of tie breaks, the more you have you will be getting near to excels accuracy for decimal places
can you post your large formula just for reference

Agreed, this is potentially overkill. Just wanted one formula that will work for all scenarios. Ex. People tied at the back of the grid may have only reached as high as 15th or 16th place up to any given point in the season, so the calculations for 1st-14th are irrelevant zeros.

I input this formula in to the score column (WDC points) and then simply ranked the (WDC position) column.

• Row 3 references the first driver and gets pulled down through row 24.
• "IF(R3="",I3," resolves any drivers who have yet to compete up until that point in the season.
• "R3+I3" adds the current race points to the previous running points total.
• "IFERROR" is used to account for this being a drivers first race of the season and not having a previous points total to add to.
• "+(D54/100" counts first place finishes up until that point in the season. Divided by 100 rather than 10 to account for anyone who was in that position more than 10 times. To avoid changing actual score.

=IF(R3="",I3,IFERROR(R3+I3+(D54/100)+(F54/1000)+(H54/10000)+(J54/100000)+(L54/1000000)+(N54/10000000)+(P54/100000000)+(R54/1000000000)+(T54/10000000000)+(V54/100000000000)+(X54/1000000000000)+(Z54/10000000000000)+(AB54/100000000000000)+(AD54/1000000000000000)+(AF54/10000000000000000)+(AH54/100000000000000000)+(AJ54/1000000000000000000)+(AL54/10000000000000000000)+(AN54/100000000000000000000)+(AP54/1E+21),R3))

 

[HorizonHunter]

Agreed, this is potentially overkill. Just wanted one formula that will work for all scenarios. Ex. People tied at the back of the grid may have only reached as high as 15th or 16th place up to any given point in the season, so the calculations for 1st-14th are irrelevant zeros.

I input this formula in to the score column (WDC points) and then simply ranked the (WDC position) column.

• Row 3 references the first driver and gets pulled down through row 24.
• "IF(R3="",I3," resolves any drivers who have yet to compete up until that point in the season.
• "R3+I3" adds the current race points to the previous running points total.
• "IFERROR" is used to account for this being a drivers first race of the season and not having a previous points total to add to.
• "+(D54/100" counts first place finishes up until that point in the season. Divided by 100 rather than 10 to account for anyone who was in that position more than 10 times. To avoid changing actual score.

=IF(R3="",I3,IFERROR(R3+I3+(D54/100)+(F54/1000)+(H54/10000)+(J54/100000)+(L54/1000000)+(N54/10000000)+(P54/100000000)+(R54/1000000000)+(T54/10000000000)+(V54/100000000000)+(X54/1000000000000)+(Z54/10000000000000)+(AB54/100000000000000)+(AD54/1000000000000000)+(AF54/10000000000000000)+(AH54/100000000000000000)+(AJ54/1000000000000000000)+(AL54/10000000000000000000)+(AN54/100000000000000000000)+(AP54/1E+21),R3))

@jimrward I actually just found a flaw that was adding the tiebreaker points from previous rounds to each subsequent round.
Formula has been updated to show:
"=IF(R3="",I3,IFERROR(R3+ROUNDDOWN(I3,0)+(D54/100)..."