+ Reply to Thread
Results 1 to 24 of 24

Golf Handicap / Scoring System

  1. #1
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Golf Handicap / Scoring System

    Hi all...new to the forum and wondering if someone can help me before I go completely mad!!

    I am trying to generate a spreadsheet whereby weekly golf scores can be added for a number of individuals, with the last n number of scores being added together. The difficulty I am having is that n is generated from another formula in a cell, and will change. Also, the row where the weekly scores will be added, will have some blanks as players do not play every weekend...so blanks need to be ignored.

    Anyone any idea how I can do this?

    Thank you in advance.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Hi Ali...thanks for this...I hope I have uploaded the example file.

    As you can see, Player A has a number of scores over the weeks...but some blanks.

    Cell BR8, counts the number of rounds (scores) the player has, and based on the number of rounds, BS8 calculates the number of rounds that count towards his adjusted handicap.

    I need to take the LOWEST n rounds (n being the number in BS8), from the last x total rounds (x being the number in BR8) (ignoring the blanks!). As additional rounds are added, the last x rounds will change...so its moving all the time.

    Is this possible?
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    It will be possible once you tell me exactly how you calculate it manually! How do you arrive at the values in BR8 and BS8 (n and x)? 20+ is not something I'd want to see in a cell that is used in a calculation, by the way. In other words, how do you determine which rounds count and which don't? I need to be able to use the logic.
    Last edited by AliGW; 08-04-2020 at 09:32 AM.

  5. #5
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Apologies...that wasn't very helpful was it!

    BR8 is simply the number of rounds played, but it tops out at 20...entering it manually I left it at 20+...but thinking about formulas, if we use the COUNT formula, but then have it maxing out at 20...is that possible? If not, maybe an additional column with the true COUNT value...then a separate column with IF more than 20 = 20?

    BS8 is then the number of counting rounds based on BR8 and the US Golf Association scoring system;

    IF rounds equal...lowest x rounds used to average out

    5 or 6...1 lowest
    7 or 8...2 lowest
    9 or 10...3
    11 or 12...4
    13 or 14...5
    15 or 16...6
    17...7
    18...8
    19...9
    20 or more...10

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Here's the first one:

    =MIN(20,COUNT($C8:$BO8))

    I'll have another look at the rest now.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    BS8:

    =LOOKUP(BR8,{0,5,7,9,11,13,15,17,18,19,20},{0,1,2,3,4,5,6,7,8,9,10})

    OK - final question: how have you decided on the rounds in red - why are they excluded from the calculation?

  8. #8
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Sorry...again...confusing and not helpful!

    That was simply an attempt to do things manually, and once I added another round, red-out an earlier round so that only the correct number of rounds are counted.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Yes, but how do I know what the correct number of rounds is?

    Oh, forget that - I get it. Give me five!

  10. #10
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    In cell BT8....it is calculated by using the last x rounds played (in this case, 20, in BR8), then taking the LOWEST n rounds of this x number (in this case 10, in BS8)...then summing this x rounds.

    So for player A...sum of lowest 10 rounds of the last 20...equals number in BT8...then divided by 10 for BU8.

    I'm sorry...I'm not doing a very good job of describing this and I really do appreciate your help.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    This will calculate the score you want:

    =IFERROR(SUM(SMALL(BO8:INDEX(C8:BO8,LARGE(COLUMN(C8:BO8)*(C8:BO8<>"")-2,BR8)),ROW(INDIRECT("1:"&BS8)))),"")
    Attached Files Attached Files

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

    Re: Golf Handicap / Scoring System

    Hi GolfTragic,

    I'd set up your problem a bit differently. You need a row for each round a player plays. Names go in Column A and Date in B and Score in C.
    Then you need to add a sort and helper column to show rounds by date, where 1 is the most recent. Then do an advanced filter to filter out rounds more than 20.
    Then do a Pivot Table showing the best of the last 20 rounds.... I'll do an example and attach it in a few minutes...

    See the attached and read the comments on how to do this process.
    Golf Best 10 of last 20 Rounds.xlsx
    Last edited by MarvinP; 08-04-2020 at 11:03 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  13. #13
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Thank you so much!

    So if i was to extend the dates, and add many more scores...I simple extend the range within this formula?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Yes - the range in RED must be the full range from C8, and the reference in GREEN must be the final cell of this range:

    =IFERROR(SUM(SMALL(BO8:INDEX(C8:BO8,LARGE(COLUMN(C8:BO8)*(C8:BO8<>"")-2,BR8)),ROW(INDIRECT("1:"&BS8)))),"")

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

    Re: Golf Handicap / Scoring System

    Hey Golf_Tragic,

    AliGW is a little smarter than me, so she can do a formula, but please look at my workbook attached above where I've done this problem many times. I keep all my score like stated in the post and example. Then I go through a process that you can read.

    Hope these suggestions help.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Marvin's solution is certainly the smarter way of working! I don't know about my being smarter than he - I would very much doubt it!

  17. #17
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Thank you both so much...going to spend the rest of the evening playing around with these!

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

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

    Also, as a relatively new member of the forum, 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.

  19. #19
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Good morning guys...me again!

    I have used the above and got to a position where I am once again at the limit of my ability and its driving me mad. I have attached the file I have produced with your help, but it is doing some funny things.

    You will see Player 1...and all his scores on the "Scores" sheet. This then updates the "Calculations" sheet. However it is not working correctly for some reason. It is working for some players, but not others; for example (I have manually calculated some of the scores):

    For Player 1, the total of lowest counting rounds should be 880...but it is showing 885.

    Player 3 should be 835...but is showing 838

    Player 6 should be 865...but is showing 866

    Player 8 should be 446...but is showing 449

    Players 4, 5, 7 and 9 are all correct.

    It is as if for some of the players, the correct lowest scores are not being identified?!

    Also, while I was doing it manually, I would highlight the lowest scores for ease of reference...can the lowest x number of scores that are being calculated be highlighted automatically?

    Thank you again in advance for your help.
    Attached Files Attached Files

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    You might wish to remove the SOLVED tag for now.

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Try this:

    =IFERROR(SUM(SMALL(Scores!ED8:INDEX(Scores!D8:ED8,LARGE(COLUMN(Scores!D8:ED8)*(Scores!D8:ED8<>"")-3,D6)),ROW(INDIRECT("1:"&E6)))),"")

    since you are now starting in column D instead of C.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    Also, while I was doing it manually, I would highlight the lowest scores for ease of reference...can the lowest x number of scores that are being calculated be highlighted automatically?
    Yes, but it's a completely different question and will require a new thread with a suitable (descriptive of what you are trying to do) title.

  23. #23
    Registered User
    Join Date
    08-04-2020
    Location
    Manchester
    MS-Off Ver
    Excel for Mac 16.39
    Posts
    13

    Re: Golf Handicap / Scoring System

    Great...thank you so much...its working again. Will mark as solved again and will start a new thread.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Golf Handicap / Scoring System

    You're welcome.

+ 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. Golf Society Leaderboard and Scoring System
    By CMAGEE94 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-19-2016, 02:23 AM
  2. Golf Handicap system
    By Ray Haynes in forum Excel General
    Replies: 8
    Last Post: 11-12-2010, 11:01 AM
  3. handicap system for golf league
    By i3putt in forum Excel General
    Replies: 17
    Last Post: 10-28-2010, 10:25 AM
  4. Golf Handicap
    By dodger999 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-09-2009, 11:45 AM
  5. [SOLVED] golf handicap
    By neo314trinity in forum Excel General
    Replies: 5
    Last Post: 03-15-2006, 02:50 PM
  6. [SOLVED] golf handicap
    By Phineus in forum Excel General
    Replies: 4
    Last Post: 07-03-2005, 11:05 PM
  7. Golf Handicap
    By Dick Gwin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2005, 11:06 AM

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