+ Reply to Thread
Results 1 to 5 of 5

head to head tiebreaker between 3 or more

  1. #1
    Registered User
    Join Date
    12-13-2010
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    head to head tiebreaker between 3 or more

    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.
    Attached Files Attached Files
    Last edited by chamber37; 12-20-2010 at 07:40 AM. Reason: additional information

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    286

    Re: head to head tiebreaker between 3 or more

    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...

  3. #3
    Registered User
    Join Date
    12-13-2010
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: head to head tiebreaker between 3 or more

    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.

  4. #4
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    286

    Re: head to head tiebreaker between 3 or more

    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?

  5. #5
    Registered User
    Join Date
    12-13-2010
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: head to head tiebreaker between 3 or more

    Quote Originally Posted by outofthehat View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1