# Golf Handicap / Scoring System

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

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

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

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

5. ## 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. ## 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. ## 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. ## Re: Golf Handicap / Scoring System

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. ## 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. ## 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. ## 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)))),"")

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

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. ## 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. ## 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. ## 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. ## Re: Golf Handicap / Scoring System

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

18. ## Re: Golf Handicap / Scoring System

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

20. ## Re: Golf Handicap / Scoring System

You might wish to remove the SOLVED tag for now.

21. ## 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. ## 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. ## 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. ## Re: Golf Handicap / Scoring System

You're welcome.

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