+ Reply to Thread
Results 1 to 33 of 33

Golf scoring formula request

  1. #1
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Golf scoring formula request

    Change to Title, to better reflect my request
    Attached Files Attached Files
    Last edited by flyerpicker; 04-22-2017 at 10:13 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf Score Keeping

    B
    C
    D
    E
    F
    G
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    V
    2
    Name
    Today
    Var
    Quota
    10th
    13 Apr
    6 Apr
    30 Mar
    23 Mar
    16 Mar
    9 Mar
    2 Mar
    23 Feb
    16 Feb
    9 Feb
    2 Feb
    26 Jan
    19 Jan
    12 Jan
    3
    Alan
    9
    (22)
    31
    14
    32
    32
    33
    31
    29
    30
    29
    4
    Barb
    24
    15
    9
    10
    11
    6
    8
    4
    5
    9
    7
    7
    10
    10
    6
    5
    Cain
    25
    15
    10
    11
    9
    11
    8
    11
    8
    7
    10
    11
    11
    8
    6
    9
    7
    6
    Dana
    26
    12
    14
    14
    16
    12
    15
    15
    14
    7
    Eric
    7
    (6)
    13
    11
    11
    11
    12
    12
    12
    13
    16
    11
    12
    13
    16
    13
    16
    8
    Fran
    25
    0
    25
    14
    24
    26
    24
    27
    23
    21
    24
    9
    Gary
    17
    12
    5
    14
    3
    4
    5
    6
    4
    0
    2
    7
    10
    Hana
    26
    2
    24
    14
    24
    23
    23
    24
    23
    24
    22
    24
    26
    11
    Ivan
    18
    8
    10
    12
    9
    7
    8
    10
    8
    6
    12
    8
    7
    15
    12
    Jane
    9
    (7)
    16
    10
    15
    16
    14
    14
    18
    16
    15
    18
    15
    14
    17
    17
    14
    13
    Kent
    17
    6
    11
    10
    11
    8
    9
    11
    9
    8
    9
    12
    11
    10
    7
    9
    14
    Leah
    27
    8
    19
    13
    19
    19
    18
    18
    15
    15
    15
    19
    19
    20
    13
    15
    Mark
    10
    (7)
    17
    12
    17
    15
    18
    16
    18
    15
    13
    16
    18
    13
    16
    Nina
    29
    2
    27
    13
    23
    28
    23
    27
    24
    26
    25
    22
    27
    27
    24
    17
    Otto
    6
    (16)
    22
    13
    20
    21
    22
    22
    23
    20
    20
    23
    23
    19
    21
    18
    Peri
    14
    (10)
    24
    14
    22
    22
    24
    24
    22
    20
    20
    24
    22
    26
    19
    Quin
    5
    (10)
    15
    14
    15
    12
    16
    15
    15
    13
    15
    14
    11
    16
    20
    Rene
    21
    (11)
    32
    14
    31
    33
    32
    31
    31
    29
    33
    28
    21
    Seth
    13
    6
    7
    14
    6
    6
    6
    5
    5
    5
    5
    8
    9


    See attached.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf Score Keeping

    Dear Shg,

    your reply looks absolutely awesome. I do have a few questions to help me better understand how this works. If you have time, I really would like to talk with you about your sheet. If you prefer to not call, I can put them into a typed reply. Certainly thanks for your efforts thus far. Jim 502-593-0395. If I dont answer, leave a message, letting me know when to call you back. :-))

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    ... or I can put them into a typed reply.
    That, please.

  5. #5
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Yours is a much newer version than my 2003. I tried a few things like inserting a new row (as in a new member, or just typing a name) and mine crashed. I would upgrade my excel if necessary to make this work. I have a 4 year old MacBook Pro.

    I guess I just need some basic instruction on how to enter data, and then how to work with the VAR column to see who won. If I clear the contents of the TODAY column, then the VAR column populated with the player’s quota expressed as a negative number.

    But anyway…
    When I sit down at the clubhouse and enter the scores:
    -which column do I enter scores in? I am guessing I should enter in the TODAY column, then have that data migrate to the column right of column G.
    - is it possible for the VAR column start out blank as I begin to enter scores? Otherwise, I will need to “overlook” non-players data that is visible.

    Speaking of sorting, when I perform a descending sort on the values in the VAR column, they result does not appear to be in any particular order.

    What is the “10th” column number? Will I need to see it for any reason?

    Again, thanks very much…. Again, feel free to call me.

    Jim

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Here's a version for Excel 2003.

    Yes, enter today's scores in the Today column. When you're ready to move on, copy the Today column, insert it to the left of the leftmost dated column, add the date as the header (or not, they are not used), and clear the scored in the Today column.

    is it possible for the VAR column start out blank as I begin to enter scores?
    Done.

    Speaking of sorting, when I perform a descending sort on the values in the VAR column, they result does not appear to be in any particular order.
    You have to sort the whole table. I deleted the blank column to make it more obvious.

    What is the “10th” column number? Will I need to see it for any reason?
    It needs to be there, but you can hide it.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Loving your work!

    Questions:

    * a player should have a quota before the round is played and then his quota should be updated after his score is posted. When does that 'update' occur?

    * I understand I can hide the '10th' column, but I am still curious what the number means, or should i just not worry about it?

    * when a new player joins the league, since he has no history, we were thinking we would just assign him a quota and go forward. Is the proper way to do this to enter 5 rounds with that pre-determined score?
    Last edited by flyerpicker; 04-23-2017 at 08:34 PM.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Quote Originally Posted by flyerpicker View Post
    a player should have a quota before the round is played and then his quota should be updated after his score is posted. When does that 'update' occur?
    When you copy the Today column and insert it among the other scores.

    Quote Originally Posted by flyerpicker View Post
    I understand I can hide the '10th' column, but I am still curious what the number means, or should i just not worry about it?
    It locates the column of the 10th oldest score.

    when a new player joins the league, since he has no history, we were thinking we would just assign him a quota and go forward. Is the proper way to do this to enter 5 rounds with that pre-determined score?
    Sounds right to me.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    I think this is a better layout:

    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    R
    S
    T
    U
    2
    Name
    10th
    Quota
    Var
    HideMe
    20 Apr
    13 Apr
    6 Apr
    30 Mar
    23 Mar
    16 Mar
    9 Mar
    2 Mar
    23 Feb
    16 Feb
    9 Feb
    2 Feb
    26 Jan
    19 Jan
    12 Jan
    3
    Gary
    14
    27
    (5)
    22
    25
    24
    25
    28
    25
    27
    29
    27
    4
    Otto
    14
    11
    (3)
    8
    10
    10
    11
    12
    12
    10
    5
    Kent
    14
    15
    (3)
    12
    13
    13
    16
    15
    14
    15
    13
    12
    15
    6
    Fran
    12
    30
    (2)
    28
    29
    27
    30
    28
    31
    25
    28
    32
    24
    27
    7
    Hana
    13
    17
    (2)
    15
    18
    16
    18
    14
    14
    15
    20
    14
    11
    9
    14
    8
    Leah
    12
    19
    (1)
    18
    18
    20
    17
    17
    19
    15
    17
    18
    19
    22
    22
    18
    9
    Jane
    13
    22
    (1)
    21
    20
    19
    24
    24
    20
    19
    21
    22
    22
    18
    24
    10
    Dana
    14
    14
    (1)
    13
    15
    17
    12
    16
    12
    14
    11
    12
    11
    Cain
    14
    21
    (1)
    20
    22
    20
    20
    21
    21
    19
    19
    21
    24
    20
    12
    Peri
    14
    22
    (1)
    21
    24
    23
    21
    22
    19
    19
    23
    21
    13
    Rene
    14
    7
    0
    7
    7
    7
    8
    11
    5
    6
    14
    Eric
    13
    10
    0
    10
    12
    9
    11
    10
    10
    10
    11
    9
    10
    10
    15
    Nina
    14
    9
    1
    10
    5
    7
    6
    6
    11
    6
    8
    10
    10
    10
    16
    Mark
    14
    29
    25
    27
    30
    28
    27
    28
    32
    27
    17
    Quin
    13
    20
    17
    20
    20
    22
    20
    19
    17
    22
    16
    19
    17
    18
    Ivan
    14
    25
    24
    27
    24
    26
    25
    25
    23
    19
    Alan
    14
    10
    9
    7
    12
    9
    10
    12
    9
    6
    11
    20
    Barb
    14
    12
    10
    11
    10
    11
    7
    13
    13
    12
    13
    10
    21
    Seth
    14
    13
    11
    10
    10
    12
    9
    10
    14
    7
    13
    16


    To add a new date,

    o Insert a column to the right of the HideMe column. Quotas will recalculate and variances will be blank.

    o Add today's scores in the inserted column. Variances will calculate as you enter them.

    o Sort the whole table ascending by the Variance column.

    Get a newer version of Excel and try that.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Evidently my problem is my mac 2011 version. i can insert rows, but when i enter a name in the first column, I get a crash.

    Can you tell me what version you created this workbook in? I sort of hate to spend the money to upgrade for this one golf workbook only, but will if i have to. Unless you have some way to write your magic in an 2003 version.
    Last edited by flyerpicker; 04-24-2017 at 09:50 PM.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Quote Originally Posted by flyerpicker View Post
    Can you tell me what version you created this workbook in?
    Excel 2010.

    ... in an 2003 version.
    Attached.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Dear Shg, everything works as advertised, but still the same issue: I am able to insert rows (as in adding new players) but when I enter a name, excel crashes and generates a crash report. Any ideas? I have to have the ability to add more rows, with names. Jim

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    I don't have any problem with either the xls version (opened in Excel 2003) or the xlsx version (opened in Excel 2010).

    Maybe someone else will wander along and try.

    Minor updates attached.
    Attached Files Attached Files

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Golf scoring formula request

    Hi,

    Both versions also crash for me using Excel 2011.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Isn't that curious, thank you. Anything to indicate why?

    Can someone try in a Windows version?

  16. #16
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Golf scoring formula request

    I'm using windows and both open for me. Excel 2016 and 2003
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  17. #17
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Seems to be a Mac thing, flyerpicker, and I've never touched one, so have no suggestion.

  18. #18
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Golf scoring formula request

    I only had time to test briefly last night. I will endeavour to determine the issue later when I have access to my Mac again.

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Golf scoring formula request

    A double named range 'scores' in which one the formula contains only #ref 's doesn't help I assume.



  20. #20
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Nor does it hurt. It's an orphaned name from a deleted sheet.

  21. #21
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Golf scoring formula request

    Did you ask the OP to test a file in which no dubious named ranges occur ?

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Golf scoring formula request

    It doesn't make a difference- the crash still occurs.

    If you turn off the 'Extend data range formats and formulas' in Excel Preferences, the crash doesn't occur. Alternatively, if you fill the formulas down from the row above before entering a name in column B, it also seems to be stable.
    Last edited by xlnitwit; 04-27-2017 at 06:27 AM.

  23. #23
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Quote Originally Posted by flyerpicker View Post
    Dear Shg, everything works as advertised, but still the same issue: I am able to insert rows (as in adding new players) but when I enter a name, excel crashes and generates a crash report. Any ideas? I have to have the ability to add more rows, with names. Jim
    You might have more success if you just add a name at the bottom of the list. The formulas will adapt.

  24. #24
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Hello Shg and others…

    Took a short vacation, sorry if I held you guys up. It appears the problem of inserting a row and/or inserting a name causing a crash has been fixed by the aforementioned change to Excel Prefs. Thank you.

    So where are we now? I attached SHG’s sheet, newly formatted and using our live names. All the math seems to work. Notice that I added a clip at the bottom to compute payouts. All that remains is for me to enter historical data for all the players.

    SO, just a small last issue: I eliminated the actual date row—for simplicity. However, when I take out the very last date, the math creating the quota in column C goes ###. So I left the date in cell R1. Can we eliminate that date and still have formulas working?
    Attached Files Attached Files

  25. #25
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    You can use numbers instead of dates as the row headers, just don't format them as text. Select F1:R1 and format as General, then enter numbers.

  26. #26
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Hi SHG…

    Not sure if I did anything wrong, but here another challenge:

    All works fine as I insert the new column and enter that day’s scores in the new column. After all entered, sorted, and payouts made, it comes time to “slide all the scores to the right” and prepare for the next week’s entries, which should include updated quotas.

    I can’t see where that is happening. The quota is not updating like it did on the samples you last posted.

    Anyway, hope you can help. I am LOST! I have attached the current live sheet, and I have it backed up

    Jim
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Hello Shg,
    Need your help.. see post # 26. Jim

  28. #28
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    It works fine for me. Can you give a specific example of a row where the quota should change but doesn't?

  29. #29
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    well, it appears i had a brain fart. Yes, the quota does update the moment I insert a new column.

    So, as i insert a new column each week (for up to 30 weeks) how should I manage the size of the sheet? Could i hide columns that march way over to the right? if you were me, what--if anything--would you do to improve this sheet? We like it really well, and it goes to work this Thursday.

    thanks for everything...
    Jim

  30. #30
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Golf scoring formula request

    Glad it works for you, you're welcome.

  31. #31
    Registered User
    Join Date
    02-08-2017
    Location
    fisherville, KY
    MS-Off Ver
    2003
    Posts
    45

    Re: Golf scoring formula request

    Hello Again My Friend!

    First , all your work has seen good times. My group of 50+ golfers are amazed at this spreadsheet that you worked on so hard. Not only is it more accurate than doing napkin math, it’s also easier and faster.

    Since you saw it last, it has grown in features, which I will explain below. But for the basic work you created that records the player’s scores, that portion is working just great.

    As you can see by looking at the attachment, I have entered about 5 weeks worth of playing. But I do have 3 very minor issues that I have been working around:

    1) I can’t sort the VAR (variance) column when populated. There must be something in that column that interferes with the sort function.

    2) The character (a zero) currently in Z1 must be doing something, because when I try to clear or delete it, the rest of the sheet goes nuts. Can this character “go away”?

    3) The players currently in rows 29 and 39 are ‘new’. I have been entering their scores and managing/entering their quota manually. Per our club rules, a player’s scores are a simple average (rounded down) until completing 5 rounds, at which time your formulas take over which is the best 5 of the last 10 rounds, rounded down. Is there any way to let the spreadsheet do this, which is to begin a player’s history with the raw average (to determine the quota) until after the 5th round and then switch to the existing formula?

    The other thing I am prayerfully requesting of you is looking for a suggestion to improve the utility of the sheet: As you can see, I have built a “payout” section in the name column. It’s located there because as we go forward in time, the spreadsheet “grows” to the left and to the right of the name column. Adding columns to the left are to record the players earnings history and columns added to the right are the historical scores. So the payout section has to “live” in the name column, otherwise it gets moved around.

    My problem is: when I complete a given week and “record” the scores by inserting a new column, I have to do lots of busy work, such as creating new headings, move cell shadings, re-sorting, etc.

    I guess I want my cake and eat it too! I am asking your opinion about how to minimize the busy work after each round. Is there a better way to arrange all this data? Point being--each week I would really like to just enter the scores, the money payouts, and then do something that records everything (like inserting the new column) so that the sheet would be ready for the next round.

    Am I asking for too much? I again thank you in advance for your assistance. Feel from to call me 502-593-0395.

    Jim Allen
    Attached Files Attached Files

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

    Re: Golf scoring formula request

    Hi Jim,

    I too am an old golfer and have done many tournaments using Excel. I've accomplished some of what you need by keeping two different sheets in the same workbook. The first I call Scores for the current day's round of golf. A second, and the one I think you need, I call Rounds. When the day is done and all scores are in and money handed out, I simply copy my (current day scores) to the bottom of the Rounds sheet. This then allows me to accumulate many rounds into a single table.

    I know how you are keeping past scores on a single row for each player, but this leads to much harder formulas and complications. I think you are expressing this by needing a 5 round rule to determine handicaps.

    You asked for an easier way to arrange your data, and I'd say that is to enter your data as one row per round. You will need to add a column of "Date Played", "Player Name", "Handicap", "Gross Score", "Net Score". Yes, you will need to add a date for each row and use Validation for Player Name, BUT this will then allow Excel to do much easier formulas. You will also then be able to add a single round to a sheet that collects all rounds.

    If you Private Message me with your email, I can send you a few examples of ones I current use, so you can see what I'm working with.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

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

    Re: Golf scoring formula request

    Hi Jim,

    You asked for a better way to collect your weekly data and attached is how I'd start doing it.

    You might need a cute formula like shg supplied but it would go on the Rounds sheet to be copied onto the Scores sheet.

    Start with this kind of data layout to make the whole process easier.

    Golf Buddies June Better Way.xls

+ 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 score cards and pairing
    By wyowhite in forum Excel General
    Replies: 0
    Last Post: 04-19-2016, 04:13 PM
  2. In over our head with golf score spreadsheet
    By tcarter1208 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2015, 06:50 PM
  3. Replies: 1
    Last Post: 11-24-2013, 07:53 AM
  4. Golf Score Card
    By wigofoshizzo in forum Excel General
    Replies: 8
    Last Post: 07-23-2011, 06:09 PM
  5. Golf Score sheet
    By kaono1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-28-2007, 03:00 PM
  6. [SOLVED] golf score
    By Bob in forum Excel General
    Replies: 1
    Last Post: 07-11-2006, 01:35 PM
  7. Golf Score Formula needs tweaking
    By kevhatch in forum Excel General
    Replies: 2
    Last Post: 04-28-2006, 07:55 PM

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