+ Reply to Thread
Results 1 to 11 of 11

Indoor Soccer League Table Tiebreaker Help

  1. #1
    Registered User
    Join Date
    07-03-2011
    Location
    PDX
    MS-Off Ver
    Excel 2007
    Posts
    9

    Unhappy Indoor Soccer League Table Tiebreaker Help

    I have spent hours searching for this, but nobody has ever given a clear answer either on this forum or anywhere on the internet. So, I figure it's finally time to buck up and post my own question to see if anybody can answer me.

    I manage an indoor soccer league and we keep a pretty basic League Standings: 3 points for a Win, 1 point for a Draw, 0 for a Loss. Our leaderboard ranks teams based on the amount of points they have. In case of ties, it CURRENTLY breaks them by the following:

    1. Most Wins (W)
    2. Fewest Losses (L)
    3. Goals Allowed (GA)
    4. Goals Scored (GF)

    However, what I want is for the 3rd tiebreaker to be HEAD-TO-HEAD results. IF TEAM A is tied with TEAM B in both Wins and Losses, the third tie breaker would be determined by whoever won the TEAM A vs TEAM B matchup. Since teams will play each other more than once, it would be possible for them to split the H2H matchups, and that's where the next tiebreakers come in: Goals Allowed and Goals Scored.

    Here is how I HOPE to have the tiebreakers:

    1. Most Wins (W)
    2. Fewest Losses (L)
    3. Head to Head Matchups
    4. Goals Allowed (GA)
    5. Goals Scored (GF)

    In my current standings, there are two teams ("Timbers Indoor" & "FC United") who are tied in Games Played, Wins, Losses, Goals For and even Goals Against. However, in their Head-to-Head matchup, "FC United" won, so they should be ranked above "Timbers Indoor." But my 'Standings' sheet simply lists "Timbers Indoor" twice.

    I use XL2007 and have attached my spreadsheet. Please let me know if you can help!

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Indoor Soccer League Table Tiebreaker Help

    Hi Jake and welcome to the forum,

    On your results page I've created a table that I think will do what you want.

    The H2H formula counts the number of wins the team in that row has over the team above it. This is then used in the Ranking Total and finally in the Rank. You will need to sort by the rank column for your answer to be valid.

    Let me know if you need more explaination.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Indoor Soccer League Table Tiebreaker Help

    Hello Jake/Marvin,

    I don't have any answer for you as yet, Jake, just some questions! On the head to head comparison if two teams have won one each is that a tie (and you then progress to tiebreak 4 GF against all teams).....or would you take into account the scores in those games, e.g. team A beats team B 1-0 then B beats A 2-0, does B win the head-to head?

    Presumably it would be possible for there to be a 3 or more way tie to be decided by head to head - how would that work? would you take into account all the games between the teams involved?
    Audere est facere

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Indoor Soccer League Table Tiebreaker Help

    Hi,

    My method to eliminate tie breakers is to add decimal values to a total score. I thought the 3 points for a win and 1 for a draw was the interger part of the ranking. I then used the goals scored against divided by 100 and added this onto the score. Then the goals scored divided by 10000 and added it.

    As for the third criteria, I was hoping to sort all the teams by their score as determined above. Then count the number of wins against the team directly above them in this sorting. Because they were sorted by score and if they were tied at that time, giving them a point for a win of the team directly above them would be the third and required, Head To Head, number the OP wanted.

    I'm hoping my H2H formula didn't move around based on the sort. I may have to rethink that or move it away from the sorting table and add it in after the score sort.

    Hope this makes sense. I do wonder about a 3 way tie. I'm not sure my formula deals with that posibility.

  5. #5
    Registered User
    Join Date
    07-03-2011
    Location
    PDX
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Indoor Soccer League Table Tiebreaker Help

    Hey guys, thanks for the help!

    Between Marvin's help and the help of a friend of mine, I've been able to break 2-way ties (as long as they play each other 2 times or less), using pretty much the same formula Marvin suggested.

    But now I'm getting much more involved with this league, and I know that I will soon be dealing with 3-way and possibly 4-way ties. Also, teams could end up playing each other more than 3 times.

    Any thoughts on how to handle these? The same friend who helped me with the H2H tie-breaker suggested a Macro, but I'd really love to stay away from that if at all possible.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Indoor Soccer League Table Tiebreaker Help

    Could you look at the questions I asked, Jake? I think it's difficult to do, in any case, especially without VBA, so I don't want to go down the wrong road.....

  7. #7
    Registered User
    Join Date
    07-03-2011
    Location
    PDX
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Indoor Soccer League Table Tiebreaker Help

    If two teams were tied H2H, then we'd advance to the third tie-breaker: Goals Against. If there was a tie there, then it would go to Goals For. Eventually, if there was still a tie, I think Record Against Common Opponents (R.A.C.O.) would come in handy, but that's a whole different formula, I'm sure.

    As for 3-way ties, I'm curious how that would be broken. For example, let's say A beats B, B beats C, and C beats A. I think in that case we'd have to skip over H2H results and go to GA and GF. That is probably where R.A.C.O. would come in handiest.

    This would be my ideal Ranking Criteria:

    1. Ranking Points (3 for a win, 1 for a draw, 0 for a loss)
    2. Most Wins
    3. Fewest Losses
    4. Head-to-Head
    5. Goals Allowed
    6. Goals Scored
    7. Record Against Common Opponents

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Indoor Soccer League Table Tiebreaker Help

    Hi Jake,

    I once tried to do this problem with Tennis Rankings and the same type of logic was needed. How about doing this.

    Every team has a number of wins. Let this number be thier value. So for the H2H sort add the values of the teams they have beaten.

    So Team 1 and Team 2 are Tied with Ranking Points and Fewest Losses.

    Team 1 has beat Team 3 (who has 3 wins) and Team 4 (who has 4 wins)
    Team 2 has beat Team 3 and Team 5 who has 3 wins also.

    Team 1 should have 7 H2H points while Team 2 has 6 H2H points. Team 1 would have a higher rank.

    Does this make sense? I wonder if it works for you system?

  9. #9
    Registered User
    Join Date
    07-03-2011
    Location
    PDX
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Indoor Soccer League Table Tiebreaker Help

    Hey Marvin,

    That sounds interesting. I once created a Kickball standings workbook that included team's Strength of Schedule, which is very similar to what it sounds like you're talking about.

    Here is a copy of that file (assuming you can discern the madness I was attempting!). OR stands for Opponent's Winning Percentage, and OOR stands for Opponent's Opponent's Winning Percentage. That way you get a real Strength of Schedule, which was great since you only played 6 teams out of 16.

    I'll have to play around with your setup to see how it works for H2H ties.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: Indoor Soccer League Table Tiebreaker Help

    See attached, seems to work - set up to work for anything up to a 6-way tie at present.

    Also, your attachment was ranking the highest number of goals conceded as better than lowest number, so I fixed that.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-03-2011
    Location
    PDX
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Indoor Soccer League Table Tiebreaker Help

    Thank you!!

    Now, all I have to do is put in a way to automatically generate a RANDOM league schedule based on the amount of teams and this puppy is complete.... But I will start a new thread for that.

    Once I get this thing pieced together, I'll share the final version since it looks like people are definitely searching for similar worksheets.

+ 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