# Golf League Hcp calculation

1. ## 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. ## 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.

3. ## 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

4. ## 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. ## 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. ## Re: Golf League Hcp calculation

I think I got it uploaded thank you

7. ## 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. ## 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

9. ## 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. ## 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. ## 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

12. ## Re: Golf League Hcp calculation

Does this right?

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

13. ## 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. ## 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. ## Re: Golf League Hcp calculation

=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}))

16. ## 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

17. ## 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

18. ## Re: Golf League Hcp calculation

=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. ## 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

20. ## Re: Golf League Hcp calculation

Thank you for all your help

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

#### 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