+ Reply to Thread
Results 1 to 20 of 20

Golf League Hcp calculation

  1. #1
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Golf League Hcp calculation

    Hello Everyone,

    I was wondering if anyone out there can help me with a formula here is what I am trying to do

    in our league we calculate hcp this way
    1. use the last 10 scores
    2. use the 5 lowest scores and get an average

    I will be starting with approx. 12 scores from previous year but not everyone has a score for every week and then I will be adding the new years to the formula

    If anyone can help I would apricate it Thank you in advance

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Golf League Hcp calculation

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

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

    Re: Golf League Hcp calculation

    Hi cjaiz and welcome to the forum.

    I can do this problem and here is how it is done. You need at least 4 columns of data: Date, Name, Score and Helper. You need to enter every round the golfer plays.

    You then need to sort the data in the main (4 columns) table by date from in descending order. Most recent dates at the top.
    Next you need to fill in the Helper column (let it be column D) with this formula. In D2 =CountIf(B$1:B2,B2) and pull this formula down to the bottom of your scores.

    Next you need to do an Advanced Filter to get the last 10 scores in a table of its own.

    Using this table from the last 10 scores you do a Pivot Table using the average of lowest 5 scores.

    If you supply a sample worksheet, I can show you...

    Hi - I've built you a sample workbook.

    See if it helps to explain how to do this...
    Using a Dynamic Named Range and VBA, you can automate this process so you just add more scores to the bottom of the main table.

    Golf Hcp 5 best of last 10 Average.xlsx
    Last edited by MarvinP; 02-16-2019 at 07:44 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    Handicap

    Previous Year Curent year Hcp Avg
    Golfer 1 w1 w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 w14 w1 w2 w3 w4 w5 w6 w7 w8 w9 w10 w11 w12 w13 w14
    73 74 80 86 72 68 71 72 75 71 69 67
    par 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72 72
    league HCP 1 2 8 14 0 -4 -1 0 3 -1 -3 -5

    the Hcp Avg would use only the -5,-3,-1,3,0,-1,-4,0,14,8 and then average the lowest 5 out of the 10

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

    Re: Golf League Hcp calculation

    Hi cjaiz,

    I built a sample with 1000 scores from 25 players and added it to my post above. Download the workbook and see if it makes sense. if you attach a sample workbook I can do more with your puzzle.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. 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 shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  6. #6
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    I think I got it uploaded thank you
    Attached Files Attached Files

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

    Re: Golf League Hcp calculation

    Hi,

    Yep, I've seen your layout of data before and it makes the problem almost impossible to solve. If you add a date column and do your data like my file attached above I can do your problem. Look at my attached and see if it makes sense. Learn Advanced Filters!? Twice!! Learn that CountIf formula and understand why it works. Private Message me with your phone number and we can talk if you don't get it at all.

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Golf League Hcp calculation

    Hi there,

    See if the attached workbook gets you moving in the right direction.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    I see how you are doing this but I have to do this for 40 golfers and want something that dosent need a macro to run need something to rearrange the layout with no blank field then I can run an average on the lowest 5 in the last group of 10 if that makes sense to you

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

    Re: Golf League Hcp calculation

    Hi,

    That is why I gave you my answer above. If you keep your original format of the data, I can't do it. Look at the file I attached above. You won't need a macro to run my answer but it surely helps. After you do the Advanced Filters a few times (after the sort) and put the helper columns in, it should make sense.

    My example is with 25 names and 1000 rounds where the scores were between 70 and 99. You really need to format your collected data like I have it.

    Marv

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Golf League Hcp calculation

    Hi again,

    The attached workbook will process your sample workbook.

    Are your 40 golfers in 40 different workbooks (if so, are the workbooks all in the same folder) or are they on 40 different worksheets within the same workbook, or are they all on the same worksheet?

    Regards,

    Greg M
    Attached Files Attached Files
    Last edited by Greg M; 02-16-2019 at 09:14 PM. Reason: Workbook added

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Golf League Hcp calculation

    Does this right?

    =AVERAGE(SMALL(B7:AC7,{1,2,3,4,5}))

  13. #13
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    I have this part figured out already but it calculates on all the selected numbers not just the last 10

  14. #14
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    all would be in the same workbook but I don't want to have to run a macro every time I would like it to calculate on its own

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Golf League Hcp calculation

    How about

    =AVERAGE(SMALL(OFFSET(AC7,,,,-MATCH(1,INDEX(1/(SUBTOTAL(2,OFFSET(AC7,,,,COLUMN(A7)-COLUMN(B7:AC7)))=10),),0)),{1,2,3,4,5}))

    Or
    =AVERAGE(SMALL(INDEX(7:7,AGGREGATE(14,6,COLUMN(B7:AC7)/(B7:AC7<>""),10)):AC7,{1,2,3,4,5}))
    Last edited by Bo_Ry; 02-17-2019 at 12:52 AM. Reason: add 2nd formula

  16. #16
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Golf League Hcp calculation

    Try:

    =AVERAGE(SMALL(IF(COLUMN(B7:AC7)>=LARGE(IF(B7:AC7<>"",COLUMN(B7:AC7)),10),B7:AC7,""),{1,2,3,4,5}))

    C+S+E
    Last edited by Phuocam; 02-17-2019 at 12:48 AM.

  17. #17
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    I figured out how to do it but I am sure there was an easer way and better way to do this
    Attached Files Attached Files

  18. #18
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Golf League Hcp calculation

    Please try

    =AVERAGE(INDEX(SMALL(INDEX(7:7,AGGREGATE(14,6,COLUMN(B7:AC7)/(B7:AC7<>""),MIN(10,COUNT(B7:AC7)))):AC7,ROW(INDIRECT("1:"&MIN(5,COUNT(B7:AC7))))),))

  19. #19
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Golf League Hcp calculation

    Hi again,


    I don't want to have to run a macro every time I would like it to calculate on its own

    Hmmm . . . not asking much, are we!

    Ok, the attached workbook will automatically recalculate the average score any time a value in the League HCP row is changed/added/deleted.

    The calculation routine is triggered by the "Workbook_SheetChange" routine, so extra "golfer worksheets" may be added to the workbook and the calculation routine will be triggered when appropriate.


    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    02-16-2019
    Location
    Point Marion, PA
    MS-Off Ver
    Office 365
    Posts
    8

    Re: Golf League Hcp calculation

    Thank you for all your help

+ 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 league stats
    By Darlingchivers in forum Excel General
    Replies: 3
    Last Post: 06-07-2017, 01:25 AM
  2. Golf League Handicap Calculation
    By Bobr31 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-21-2015, 03:13 PM
  3. Golf League Handicaps
    By JRHOG in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-25-2014, 01:34 PM
  4. Spreadsheet for my golf League
    By DuncanP in forum Excel General
    Replies: 4
    Last Post: 03-30-2012, 10:39 AM
  5. Golf League handicap with a twist
    By jdaubs in forum Excel General
    Replies: 2
    Last Post: 05-11-2007, 03:18 PM
  6. Golf League Help
    By peterbarone in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2005, 12:26 PM
  7. [SOLVED] Golf League Schedule
    By Golf League Schedule in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-20-2005, 11:06 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