+ Reply to Thread
Results 1 to 14 of 14

Breaking ties in a head to head contest

  1. #1
    Registered User
    Join Date
    01-24-2014
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    13

    Breaking ties in a head to head contest

    I am working on a wrestling round robin tournament bracket. What I need to do is determine place winners with the first tiebreaker being results from head to head matches. I've tried assigning points for wins, similar to hockey and several other methods, but when it comes down to how to efficiently rank the places I am struggling. I have set it up so it can be either a 5 or 6 man round robin, which means there could be a potential for multiple ties and tie breaker methods. Any help would be greatly appreciated.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    Hi and welcome ot the forum

    Hard to offer specific advice without seei8ng a sample of what you are working with, and what your expected outcome would be

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-24-2014
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Breaking ties in a head to head contest

    Thanks for the quick reply. I had it formatted to fit onto 11x17 sheets for posting on walls, etc. All of the "working" data is below the pretty part. All of the data/ names in the round above is made up. To determine a winner it goes by total wins. If there is a tie in wins it goes to winner of head to head matches. If there would be three ties and no clear winner by head to head it will go to total falls(pins) and then by least points. I am fairly confident that once I get past the head to head comparison I can do vlookups and or ranks for the other tiebreakers.

    I did enter the place winners as they should be with the current dummy values in the file.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    Thanks for the file

    1st, you can change the "Wins" P4=SUM(P50:Q50) to =COUNTIF($D$4:$D$44,M4)+COUNTIF($I$4:$I$44,M4)...then you can do away witrh that table below

    2nd, the "falls" =COUNTIF($E$50:$E$56,C49) is pointing to a blank cell (C49), and I think this ill work better and do away with that 2nd table as well...
    =SUMIF($B$3:$B$90,M4,$A$3:$A$90)
    or maybe this...
    =COUNTIFS($B$3:$B$90,M4,$A$3:$A$90,"<>""")

  5. #5
    Registered User
    Join Date
    01-24-2014
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Breaking ties in a head to head contest

    The second formula is pointing to an empty cell as it will only be filled in if one of the wrestlers wins by a pin. I what my intention for this is that all of the scores/results will be added in the cells from C61:C104 and then they will automatically update in the "clean" brackets above for quick display/printing. I realized that I need to determine a better way to show if a wrestler has a pin in the upper round bracket results.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    Not to undo all the obvious hard work you have put into this, but maybe we can clean it up and streamline it a bit. Im not convinced that we need all those calcs down the bottom (but not knowing exactly what you are doing, I could be wrong lol)

    Take me through what you are doing, step be step please (slowly lol)

  7. #7
    Registered User
    Join Date
    01-24-2014
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Breaking ties in a head to head contest

    I do not mind undoing all of the work below as it hasn't lead to where I would like to go. I was trying to determine the "winner" in a three win tie scenario in the work below, but other than a tone of IF(OR or IF(ANDs I'm not sure if there is an easy way to compare the head to head result. I guessed that if I could make it work for three wins I could make it work for the other win totals be replacing in the win total in the appropriate place in the function. The work below look unorganized because I was trying different methods to try to get there.

    Also, I am "cleaning up" how to display the fall/pin for each match in the upper brackets as I realized that I did not account for the scores automatically populating from below.

    My ultimate goal, if it is even possible, is to have the place winners automatically entered/ranked based on the results of the tournament matches. The places are determined by the following criteria;

    1-Number of wins
    If there is a tie in wins, no matter if it is 2, 3 or 4 wrestlers at one win total or a combination of wrestlers at two different wins totals.

    1st tiebreaker-result of head to head match
    2nd-If there is no clear winner in a multiple tie scenario would be total number of falls
    3rd tiebreaker is least number of points allowed in all of the matches.

    In a 5 man round robin a wrestler with 4 wins will be the champ as there is no way for another wrestler to have four wins as well.
    In a 6 man round robin a wrestler with 5 wins will be the champ for the same reason.
    Also, in a round robin tournament only one wrestler could potentially have zero wins.


    All of that being said, step one is determining the proper rank for wrestlers with the same win total/s.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    I am out of time right now, but I am uploading what I have so far so I can continue at home later
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    OK I think I have it now. To manage the tie-breaks, I did this...
    1. combined (not added) wins and pins (4 wins and 40 pins = 440)
    2. for the least points given away, I actually inverted that and said each wrestler has 100 points, how many do they have left, so 6 given = 94 left
    3. I then combined 1) with 2)...440 wins/pins and 94 left = 44094.

    Based on that, it now becomes a simple matter to find the largest, 2nd largest etc
    Give it a test and let me know?

    Edit: forgot to add...
    - I copied your sheet2, and used that for my changes
    - I added a few extra helper columns in sheet1 K:N, these pull the data from sheet2
    - then in sheet2, I used E as a helper to pull the "given" points, you can hide this if you want, or set it to white font/white background
    - I did not use any of the formulas and "stuff" that you have at the bottom of sheet2, so you can delete that if you want
    Attached Files Attached Files
    Last edited by FDibbins; 01-26-2014 at 02:19 AM.

  10. #10
    Registered User
    Join Date
    01-24-2014
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Breaking ties in a head to head contest

    I took a look at the file and when I changed one of the wrestlers win totals the formula did not work anymore. When I changed the match result the head to head results changed but did not adjust. I have uploaded the file you uploaded last with my changes in the match results highlighted along with what the places should be in green. The places in orange are what the formulas calculated.

    In the highlighted file there is a three-way tie, but one of the wrestlers lost to the other two so the head to head tiebreaker works, if each of the three wrestlers had one win among the head to heads the next tiebreaker would then be used.

    I also took out the references to the c drive in the formulas and all of the wins in the first sheet went to one. Not sure what happened there...

    The first sheet is basically a dumping ground for the general info about the wrestlers. As there can be any number of brackets depending on the total number of wrestlers I am hoping to keep the calculations on one sheet so the sheet can be copied as many times as needed and updated with the wrestlers info.

    I did update the file so that all of the cells above are driven by formulas and all of the changeable data would be added below in the box with the bold border. I also added check boxes for falls so the person updating the match results wouldn't have to type so much. I uploaded the updated file as well if you want to see what can be done in that file...

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    Thanks for the feedback Again, (sorry) I am out of time right now, so I will take a look when I get home

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    OK um...when you used my formulas in your actual sheet, you forgot to include the sheet names in the references
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    01-24-2014
    Location
    WI
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Breaking ties in a head to head contest

    I added your formulas to the second sheet for the wrestlers in the bracket. The formula you have will be great for the points against tiebreaker, but not for the head to head.

    In a tie we need to evaluate the result of the match between the wrestlers that are tied directly. The points scored in the match or if it was won by fall do not matter just the result. If there is a three way tie with all of the wrestler still having equal wins it would move to the next tiebreaker.

    For ties it may be easier to imagine the tied wrestlers in their own smaller bracket.
    Attached Files Attached Files

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,987

    Re: Breaking ties in a head to head contest

    OK I didnt catch that you had 2 different tournaments, sorry.

    I see that in in some cases you have a bye. For those, do you want the 1 name to be considered as a "win" or not? I am adding a formula that pulls out the winner of each match (to count wins), and if you want the name with a bye to be "winner", then we would use this...
    =IF(C61="",B61,IF(C61>C62,B61,B62))
    if not, then just this...
    =IF(C61>C62,B61,B62)

    I have based the rest on not considering a bye as a win. If this is how you want it, delete the formulas in F61:F103 and just hide the formulas in D61:E103. If not, use the F formula in E, and delete F
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to account for head to head matchup in Excel with other tie breakers
    By Biogeek1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2013, 05:18 PM
  2. Head to Head (Tiebreakers) in Champions League
    By jovisb1993 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2013, 06:50 PM
  3. Calculate average of last two outcomes of last two head-to-head clashes
    By wishkey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2013, 08:17 AM
  4. Ranking a league table by head to head results
    By pajc72 in forum Excel General
    Replies: 1
    Last Post: 09-02-2011, 07:02 AM
  5. head to head tiebreaker between 3 or more
    By chamber37 in forum Excel General
    Replies: 4
    Last Post: 12-21-2010, 06:42 AM

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