+ Reply to Thread
Results 1 to 9 of 9

Golf Handicap system

  1. #1
    Registered User
    Join Date
    10-20-2010
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    7

    Golf Handicap system

    Originally posted on the end of this thread.


    Forgive me for butting in - but, I have a similar problem. I too would like to average golf scores using a spreadsheet. Instead of 5 scores - our group uses the 4 lowest of the last 6 scores though. I have data on 2 spreadsheets where one is all of the scores with the latest score added to the end column. The other sheet I also insert a new column and enter the latest scores in that column. I can use either method - but, would prefer the one where a new column is inserted each time I enter scores. Like the other guy - I have players that do not play on each occasion and so there is nothing entered for them in that date. That results in blank cells that must be ignored to get the 6 latest scores for the calculations. One additional thing I would like to be able to do is to either bold, paint the cell or copy the 4 scores that were used for the calculations to a cell below where they were taken from. My final average will be displayed in the 2nd column rounded up or down to the nearest whole number. The next time scores are entered - I would like to remove that highlight from the previous calculations. Any suggestions would be a big help.

    Ray
    Last edited by romperstomper; 11-12-2010 at 11:02 AM. Reason: mark solved

  2. #2
    Registered User
    Join Date
    10-20-2010
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: handicap system for golf league

    I have a spreadsheet that is used to average golf scores using the 4 lowest of the last 6 scores played. The formula used to do the calculations was plagiarized from several responses to Forum questions answered by daddylonglegs and works very well (thank you daddylonglegs). I would like to further enhance my spreadsheet by coloring the cells of the 1,2,3,or 4 scores used to do the averages using a Conditional Format formula. Currently - that is accomplished by manually copying those 4 scores and putting them in the row below each players scores. See attached spreadsheet.

    I have data on 2 spreadsheets where one is all of the scores with the latest score added to the end column (the one attached). The other sheet I also insert a new column and enter the latest scores in that column. I can use either method - but, would prefer the one where a new column is inserted each time I enter scores. I have players that do not play on each occasion and so there is nothing entered for them in that date. That results in blank cells that must be ignored to get the 6 latest scores for the calculations. My final average is displayed in the 2nd column rounded up or down to the nearest whole number. The next time scores are entered - I would like to remove that highlight from the previous calculations. Any suggestions would be a big help.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-20-2010
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: handicap system for golf league

    Quote Originally Posted by Ray Haynes View Post
    Forgive me for butting in - but, I have a similar problem. I too would like to average golf scores using a spreadsheet. Instead of 5 scores - our group uses the 4 lowest of the last 6 scores though. I have data on 2 spreadsheets where one is all of the scores with the latest score added to the end column. The other sheet I also insert a new column and enter the latest scores in that column. I can use either method - but, would prefer the one where a new column is inserted each time I enter scores. Like the other guy - I have players that do not play on each occasion and so there is nothing entered for them in that date. That results in blank cells that must be ignored to get the 6 latest scores for the calculations. One additional thing I would like to be able to do is to either bold, paint the cell or copy the 4 scores that were used for the calculations to a cell below where they were taken from. My final average will be displayed in the 2nd column rounded up or down to the nearest whole number. The next time scores are entered - I would like to remove that highlight from the previous calculations. Any suggestions would be a big help.

    Ray
    "Bump no response"

  4. #4
    Registered User
    Join Date
    10-20-2010
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: handicap system for golf league

    I just got an error message in Excel that says "YOU MAY NOT USE UNIONS, INTERSECTIONS, OR ARRAY FOR CONDITIONAL FORMATTING CRITERIA". I don't know how to get around that though, because it is the results of that equation that would determine just what cells on the spreadsheet that I want it to color! The equation I am trying to use is, "=(SMALL(INDEX(D2:Y2,LARGE(IF(D2:Y2<>"",COLUMN(D2:Y2)-COLUMN(D2)+1),6)):Y2,{1,2,3,4})", without the quotes. That "{1,2,3,4}" are the referenced locations that I want the conditional formating to paint green to show which values were used in my calculations. Does anyone have any ideas as to how to solve this problem?

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Golf Handicap system

    I have moved your post(s) to a new question since it violated Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    10-20-2010
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Golf Handicap system

    I have continued to fool around with the problems I I'm having with my golf score averages. So far I have still not been able to resolve the issues though. If I use "=ISNUMBER(MATCH(F5,SMALL(INDEX($F5:$IV5,LARGE(IF($F5:$IV5<>"",COLUMN($F5:$IV5)-COLUMN($F5)+1),6)):$IV5,COLUMN($A:$D)),0))" in the conditional formatting - it will "kind of" work. However, it not only colors the cells with the 4 scores I am using to get my current average for each player - but, it also colors any other cell in the row that contains an identical score. I have had no suggestions from anyone on the Forum (so far) of any other way to color those cells. I'm wondering if maybe I should just use a Macro and be done with it. I could sure use some help here.

    Ray Haynes

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Golf Handicap system

    How's this? I had to add helper columns to make it work.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-20-2010
    Location
    Cincinnati, Ohio
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Golf Handicap system

    romperstomper - I AM NOT WORTHY!!! You have managed to do in a few moments what I could not figure out how to do in 3 weeks! Even though I had done such a poor job of explaining what it was that I was trying to do - you used the "tail to wag the dog" and came up with a solution that was so crisp and clean and easy to follow that I managed to use it to do exactly what I wanted. I will attach a copy of the spreadsheet to show you exactly how I incorporated those changes (if I can figure out how to do that!). Thanks again for your help. - Ray Haynes
    Attached Files Attached Files

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Golf Handicap system

    Glad to help.
    I will mark this solved, as I don't think you will be able to edit any more.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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