Help on formula in Golf Spread sheet - Golf knowledge needed

[damian_r_Home]

I think anyone who could help me on this may really need golf knowledge (especially Stapleford scoring system)

Above is a basic spreadsheet that I’m trying to create for me and a few friends in which we are going to log all our golf scores.

The problem I’m having is getting the Stapleford points bit to work in cell H6, by using the handicap score in cell E6 against the hole difficulty in cell H4.

Basically you are allowed an extra number of shoots on your round of golf (this is your handicap). Depending on the difficulty of the hole (ranges from 1 to 18, 1 being the hardest) it depends on the number of extra shots you get.
Example being Handicap of 28 you get one extra shot on every hole, which uses up 18 of you additional shots, and then another shot on holes difficulties 1 to 8. Someone with a handicap of 9 would only get an extra shot on hole difficulties 1 to 9.
Right, are you still with me?
The number of points you get now depends on the number of shots you took. i.e. the par for the hole + the number of extra shots you were allowed = your par.
If you took the same number of shots as your par you get 2 points, 1 shot above your par you get 1 point, 2 shots over and more you get no points. For every shot under your par you get another point i.e. 1 shot under = 3 points, 2 shots under = 4 points, etc, etc.
Now you see why I think someone with golf knowledge really may be the only ones who can help here.

If necessary I would be very happy to send the spreadsheet I’ve done so far (less all my failed formulas and lookups) if it makes it easier for anyone to help me.

Thanks in advance

Damian

 

[Everett99]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

You know how to tell when a golfer is lying? His lips are moving. boooo hisss.

Anyway, I'm guessing your objective is to calculate a correct handicap for each player using the Stapleford system? Since golfers are always trying to increase their handicap so they can win tournaments (I have no idea how I golfed a 68 when my average is 105). Dirty cheaters.

I am not having much luck finding the mechanics of the Stapleford Scoring system. Can you suggest a link?

I did find this, can you confirm that it is correct?

....no score worse than net bogey) and any single round can't drop your handicap more than 0.1 points. So there's no chance for a "professional" sandbagger.?....

 

[PeterMG]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

Hello Damian,

If you send me your spreadsheet, I'll come up with your solution for you.

I have developed a program in Excel to keep your own golf stats - TAP-IN Golf Stats.
Go to www.tapingolfstats.com for a look.

Send your spreadsheet to petermg@optusnet.com.au

Regards,
Peter

 

[Craig92604]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

I am an advid golfer but don't completely understand the stapleford system.

If you can provide more detail gold knowledge is not neccesary.

Book1
	D	E	F	G	H
3	Player	H'Cap	TeesPlayed	Par	Index
4				4	17
5				Score	ST'DPoints
6	PlayerA	22		4
7	PlayerB	28		8
8	PlayerC	28		6
Sheet4

 

[bravos_1]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

OK.. so some nits to pick upfront before I get to the solution
1. The scoring system you are referring to is a Stableford, not a Stapleford
2. Your understanding of how a golf handicap works is not really accurate, as a golfers handicap does not give you the shots from par from any golf course... it gives you a starting point to which you can compare what you should score on one particular course.. but you use it to calculate your course handicap (which is different from your standard handicap). This takes into consideration slope and rating.. but enough about that (more can probably be gotten from the USGA site).

For simplicity, you can just use your standard handicaps like this if you are all playing from the same tees. A Stableford as you tried to explain is a pt system in which better scores on a hole get you more points. This system varies greatly in the points given, but as example, a bogey (+1 relative to par) is worth 1 pt, par = 2pts, birdie = 3pts, eagle = 5pts, dbl eagle = 8pts and dbl bogey or worse gets you zero pts.

I have not downloaded the HTML/spreadsheet upload program so I will just tell you what I did.

1. You need to first calcualte what the "modified" par is for a given hole and player handicap.
Assuming that the players handicap is in A2, the hole rating (difficulty) is in B2, the hole's par value (ie par 5) is in C2 you would have the following code to calculate "this persons modified par for this whole". This formula is in cell D2.

=IF((MOD($A2,18))>$B2,1,0) + FLOOR(($A2/18),1) +$C2

I would suggest calculating this in a sep cell and hiding if you wish just to keep the formula complexity down. For this example, I will store this in cell D2. If you need the formula explained, please ask.

2. After that is done, you need to determine the diff in the actual score to determine what the stableford pts should be. Actual score is in cell E2. I am also using the same scoreing system (0.1.2.3.5.8) I mentioned above so please adjust to your scoring system. This is done using a nested if statement. Please keeping mind that nested if will only go 7 levels deep if you wish to modify.

=IF(($D2-$E2)>=3,8,IF(($D2-$E2)=2,5, _
IF(($D2-$E2)=1,3,IF(($D2-$E2)=0,2, _
IF(($D2-$E2)=-1,1,0)))))

Whatever cell you place this in will be your stableford points scored.

 

[Brian from Maui]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

If you're scoring points based on holes and using handicaps, you'll have to include the handicap for the holes included in your calculations. I got a system, but only if you want it....

 

[bravos_1]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

The hole handicap was already taken into consideration when creating the "modified par for that hole for that user".

 =IF((MOD($A2,18))>$B2,1,0) + FLOOR(($A2/18),1) +$C2

does the following:

first lets look at FLOOR(($A2/18,1) - this says to determine how many strokes to add to ALL holes. So if a player's handicap is 18, the value will be 1, if the players handicap is 25 it still returns 1 since his handicap must be 36 before he gets 2 strokes on every hole. If the handicap is 10, the value here is 0 because he only gets strokes on the 10 toughest holes, not ALL holes. With me so far?

next:
IF((MOD($A2,18))>$B2,1,0) - this determines if a player should get 1 stroke added for that hole because the handicap requires it to be added because of the hole rating (this takes care of the case of the 10 Handicapper getting a stroke for each of the 10 toughest holes and a 22 handicapper getting 1 stroke each for the four toughest, in addition to the 1 already given for ALL holes). The code say give me the remainder of (handicap/18 holes) so if handicap is 10, remainder is 10 and he gets a stroke unless hole rating is greater than 10. If the handicap is 25, the remainder is 7. This guy gets a stroke on any hole that is not greater than 7 (plus the one he got above using the floor function) so on the toughest hole, he would get 2 strokes total (one from FLOOR and one from MOD).

the last is the actual par number (ie 5)

so if the handicap is 20 and the hole rating is 2 and it is a par 5, he gets 1 stroke per hole (taken care of by the FLOOR code), plus 1 stroke because this hole uses up his last remaining handicap stroke (18+2). So his "modified" or adjusted par for that whole is 7.

Make sense?

You then compare that to the actual score posted using the nested if I also included in my original post.

BTW, this code is tested and verified.

 

[Brian from Maui]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

So where am I going wrong here?

Book2
	A	B	C	D
1	Hndcp	hndcphole	score	net
2	18	10	5	6
Sheet1

 

[bravos_1]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

You are missing some data unless "score" is really what the hole's par value is (ie 3,4,or 5) and not what the person actually scored. If that is true then everything is fine and "net" is what this pesrons adjusted par value is for this hole. If "score" is what the player actually shot, then you are using the wrong data for this formula as you should be using the value that represents what the course defines as par for the hole in question.

 

[Brian from Maui]

Re: Help on formula in Golf Spread sheet - Golf knowledge ne

You are missing some data unless "score" is really what the hole's par value is (ie 3,4,or 5) and not what the person actually scored. If that is true then everything is fine and "net" is what this pesrons adjusted par value is for this hole. If "score" is what the player actually shot, then you are using the wrong data for this formula as you should be using the value that represents what the course defines as par for the hole in question.

OK! got it. You just have the scoring points but no net score for the hole.