head to head tiebreaker between 3 or more

**chamber37** · 12-20-2010, 06:37 AM

Before I begin, let me say this might be a little confusing, but the file is attached. It might make more sense if you read while viewing the file - which can be unprotected without a password (its only there to prevent mistakes while updating results). Thanks.

EDIT: There is a hidden sheet also, called dummy standings. I forgot to put that in the original post, sorry!

OK, so I have a spreadsheet - in Excel 2003 if this will have any impact, I'm not sure - which calculates the standings for a simulation sports league I help to run (mainly to save time, especially if I or the league commish is not available to do them) based on user inputting the results... and I've had trouble adding a head-to-head tiebreaker system in. I've managed to get the system working for tiebreakers between two teams, but if there are more than two teams to be split, the system fails. So in a nutshell, my problem is "How can I implement a tiebreaker between three or more teams in my league?"

Currently, for the H2H tiebreaker, I lookup the scores between teams, and multiply by one if the teams are currently tied in rank, and by zero if they are not. This works for breaking ties between two teams, but if three or more are tied, there is a problem due to it counting point differential in games between ALL the teams which are tied.

Sorry if that doesnt make much sense, but its sort of hard to explain. I'm sure there is a way, since I have all the results in the file too... but I'm stumped as to how to do it now.

Thanks for your time.

**outofthehat** · 12-20-2010, 06:59 AM

It depends on the precise rules for your tiebreaker. I put together something for the South Africa World Cup group stages, but the tiebreak rules are pretty involved.
Click here, and look at Article 17, subsection 6.
You can't build the spreadsheet until the rules are spelled out clearly...

**chamber37** · 12-20-2010, 07:39 AM

Thanks for the reply.

Basically the ranks are decided as follows:

Total number of wins
Number of wins in matches between the teams
Point differential in matches between the teams
Overall point differential
Points scored
Points scored away from home

At the moment, the tiebreakers are all set up in individual columns (on the dummy standings sheet) starting with wins.

I use a sumproduct formula to find how many rows have an equal value in the wins column AND a superior value in the head to head column

For example, if the wins rank is in column A, and the head to head point differential is in column B:

Please Login or Register  to view this content.

This works perfectly for seperating two teams based on head to head results, however, the situation I seem to get a problem in, is when there is a tiebreaker between three or more teams, and they havent all played each other.

When that happens, the formula above will rank a team who has played no matches against the other tied teams the same as it would a team which has drawn all its games.

**outofthehat** · 12-21-2010, 12:02 AM

But according to your tiebreak rules, they should be ranked equally. There is no mention anywhere of 'number of matches played between the teams' - if you wish to add this in as an additional tiebreak rule, then you could add another tiebreak column to count the number of matches between teams?

**chamber37** · 12-21-2010, 06:42 AM

Originally Posted by outofthehat

But according to your tiebreak rules, they should be ranked equally. There is no mention anywhere of 'number of matches played between the teams' - if you wish to add this in as an additional tiebreak rule, then you could add another tiebreak column to count the number of matches between teams?

You know... I completely forgot to take that into account. I'll try adding that in, and see if it fixes the problem. Typical of me to overlook something that simple.

Thanks, I'll get back to you as soon as I've had chance to test this.