+ Reply to Thread
Results 1 to 11 of 11

Analyzing Golf Results

  1. 12-23-2020, 09:10 AM #1
    apple405
    apple405 is offline
    Registered User
    Join Date
    08-13-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Analyzing Golf Results

    Hi there,

    I am currently trying to create some Power Ratings for golf competitions on excel. However, I am having trouble converting the results into a format for my Power Ratings algorithm to work on. As you will see in the example, the results I am getting are on my left (location of competition and score for each player that played that comp). In order for my algorithm to work I need the results in the format on the right (I have typed this manually). (The location of the competition is very important as I am trying to assess home advantage etc.). For example, 5 players might play 1 comp in England, and I need the results in head to head format eg. Player 1 72 score vs Player 2 69 score. Basically I need help on excel using formulae to get the results from the left into the format on the right. There are over 100 competitions as well so I would like to avoid typing the location of the competition into the formula every time. Thanks very much for your help in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-23-2020, 11:33 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Analyzing Golf Results

    I don't see the connection between the first block and the second block..

    Four rows for Ireland become 6, Brian appears once on the left block and 3 times on the right... without any obvious pattern...


    Please explain!!
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 12-23-2020, 11:47 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Analyzing Golf Results

    I see the connection now. I suspect that this may need VBA. A formula may be very messy, indeed.
    Reply With QuoteRegister To Reply
  4. 12-23-2020, 12:05 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Analyzing Golf Results

    When you have multiple tournaments in the same country (especially consecutive) how will you distinguish between them in your data or will you be grouping them all together?

    Like Glenn, I'm not sure if this can be done with formulas. It's an interesting permutation problem so will give it a go.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  5. 12-23-2020, 12:09 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Analyzing Golf Results

    I have the first 3 columns done. That was reasonably do-able (with helper columns!!). Struggling with the last 2...
    Reply With QuoteRegister To Reply
  6. 12-23-2020, 12:28 PM #6
    apple405
    apple405 is offline
    Registered User
    Join Date
    08-13-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Analyzing Golf Results

    Hi guys, thanks very much for your help!
    The competitions in the same country isn't really an issue, I have planned to sort that at a later date.
    I'd certainly be happy to use VBA on this if it was to make life easier!
    Once again, thanks for your help!
    Reply With QuoteRegister To Reply
  7. 12-23-2020, 02:28 PM #7
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Analyzing Golf Results

    For MS 365 with LET function.

    Please try at
    F2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    I2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    G2
    =SUMIFS(D2:D14,B2:B14,F2#,C2:C14,I2:J23)

    J2
    =INDEX(FILTER($C$2:$C$14,$B$2:$B$14=F2),1+COUNTIFS($B$2:$B$14,F2)-COUNTIFS(F$2:F2,F2,I$2:I2,I2))


    Without LET function
    O2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    R2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    P2
    =SUMIFS(D2:D14,B2:B14,O2#,C2:C14,R2:S23)

    S2
    =INDEX(FILTER($C$2:$C$14,$B$2:$B$14=O2),1+COUNTIFS($B$2:$B$14,O2)-COUNTIFS(O$2:O2,O2,R$2:R2,R2))
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  8. 12-25-2020, 04:35 PM #8
    apple405
    apple405 is offline
    Registered User
    Join Date
    08-13-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Analyzing Golf Results

    Thanks very much for your help, that is great, works very well!

    Do you think going forward the formulae or VBA is the best option for this power rating, seeing as I am likely going to have to adapt it a few times?
    Reply With QuoteRegister To Reply
  9. 12-25-2020, 07:32 PM #9
    apple405
    apple405 is offline
    Registered User
    Join Date
    08-13-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Analyzing Golf Results

    Quote Originally Posted by Bo_Ry View Post
    For MS 365 with LET function.

    Please try at
    F2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    So I ran a test run with 101 golf scores from various competitions. This formula worked for B2:B56 but not B2:B57 or after, instead it returned #Num!
    With B2:B56 there will be less than 101 results (in column F), but with B2:B57 it appears there would be more than 101 results in column F. I presume this has something to do with it. Any tips on getting around this?

    Thanks for your help so far!
    Reply With QuoteRegister To Reply
  10. 12-26-2020, 03:16 PM #10
    apple405
    apple405 is offline
    Registered User
    Join Date
    08-13-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    8

    Re: Analyzing Golf Results

    I've resolved it actually!
    Reply With QuoteRegister To Reply
  11. 12-26-2020, 05:56 PM #11
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Analyzing Golf Results

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Populate a sheet of results of golf players scores relative to an input date
    By PatrickMinto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2019, 08:01 AM
  2. season long golf results
    By daybaron in forum Excel General
    Replies: 1
    Last Post: 03-27-2019, 03:48 PM
  3. [SOLVED] Pivot Table for Analyzing Survey Results by Manager
    By GregStewartPTC in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-25-2019, 10:16 AM
  4. to sort hdcp figures in golf results using excel 2007
    By Debes7 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-28-2018, 05:00 PM
  5. Spreadsheet to show the results of golf competitions
    By preggy2001 in forum Excel General
    Replies: 40
    Last Post: 08-08-2018, 01:42 AM
  6. Looking for way to calculate golf tournament results
    By scbgolf in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-06-2014, 09:11 PM
  7. Analyzing Results Data
    By jharaldson in forum Excel General
    Replies: 3
    Last Post: 10-23-2013, 05:24 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1