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

 

[damian_r_Home]

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

Wow ! i check the post this morning and you've all put so much there it's gonna take a while to digest.

I'll have a play with this and let you all know how it goes.

Thanks for the help

Damian

 

[damian_r_Home]

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

Evening everyone.

Right, I’ve had a play around with all the info you were kind enough to post earlier today and I'm not sure if there is an error in the code provided by Bravos_1 or if I have done something wrong (which is more likely)

In the image below you will see that hole 5 is a par 4 and a stroke index of 10. By my reckoning this would mean that player Tom, with a handicap of 28, is due two extra shots making the +par 6 and not five as shown, which would give him 1 point.
What have I done wrong????

Bravos_1, you out there????????

Damian

 

[GorD]

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

BTW, this code is tested and verified.

Never the less it seems to be missing a "=" sign

Modify it to

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

Could you use Colo's HTML maker to post samples rather than screenshots.

With the HTML maker, we can copy and paste your data to a spreadsheet to work on, which we cant with a screenshot picture.

Not many people will sit and retype a large spreadsheet - or at least less will

 

[damian_r_Home]

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

Thanks for the help GorD - should anyone want a copy of the finished item(although i reckon most of you could write it yourself by the looks of it) just PM me.

Once again, thanks to all who posted.

Damian

 

[parry]

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

Hi all, theres certainly been some good input in this thread. Thats a nice formula bravos_1 to calculate a modified handicap and a good way to approach this problem. Well done GorD on seeing the issue when the index=handicap as I also noticed this when calculating the stableford.

Heres an example for you using bravos_1 formula to calculate a modified handicap. The AllInOneStableford column is where I have combined the formulas in Columns D&F to calculate the stableford so you can dispense with the Modified par column if you wish.

Note that using formulas isnt perfect as someone could ring a hole and get 1 point where they shouldnt. You would need a way to negate this (by perhaps adding 1 to the ringed score value) when the data is entered in the score column.

Stableford.xls
	A	B	C	D	E	F	G	H	I
1	Hole	StrokeIndex	Par	ModifiedPar	Score	Stableford	AllInOneStableford	Handicap	20
2	1	1	4	6	6	2	2
3	2	2	4	6	5	3	3
4	3	3	4	5	4	3	3
5	4	4	4	5	7	0	0
6	5	5	4	5	5	2	2
7	6	6	4	5	8	0	0
8	7	7	4	5	5	2	2
9	8	8	4	5	8	0	0
10	9	9	4	5	5	2	2
11	10	10	4	5	8	0	0
12	11	11	4	5	5	2	2
13	12	12	4	5	8	0	0
14	13	13	4	5	5	2	2
15	14	14	4	5	8	0	0
16	15	15	4	5	5	2	2
17	16	16	4	5	3	4	4
18	17	17	4	5	6	1	1
19	18	18	4	5	7	0	0
20		Totals	72	92	108	25	25
Sheet1

 

[damian_r]

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

Hi folks.

Is it me or do the Americans score stableford differently to the UK?

If you look at previous posts here you will notice that (i think it was Bravos) scored points as follows:-

2 or more over relative par scores 0
1 over relative par scores 1
equal to relative par scores 2
1 under relative par scores 3
2 under relative par scores 5
3 under relative par scores 8

. . . . . while a few UK golf club web sites i have checked on score it as follows:-
2 or more over relative par scores 0
1 over relative par scores 1
equal to relative par scores 2
1 under relative par scores 3
2 under relative par scores 4
3 under relative par scores 5
and so on and so on....

Which is right or are both right depending where you are playing as currently my spreadsheet is the 'American' way??

(Once sorted i'll send to those of you who have asked for it)

Damian

 

[GorD]

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

Damian, you are right we in the UK use the scoring as you have stated. Bravo_1 does say that we would need to adjust the points to our particular scoring system.

2pts par (net)
3pts birdie (net)
4pts eagle (net)
5pts albatross (net)
6pts (Aye, you should be so lucky)

Also not sure what Parry is refering to with the "ringing Holes". Does that refer to Mulligans ?- if so these don't exist in the UK either

 

[damian_r]

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

Mulligans?!?!?!?!?!?!?

To be honest i just go out and enjoy the walk, fresh air and the company rather than take the game too seriously!!!! (after all the way i play i can't take it seriously).

Right time to alter the spreadsheet again


Damian

 

[normg]

hi would this help www.junipergreen.net

 

[damian_r]

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

Hi normg.

I found a link to that spreadsheet on another Mr Excel topic when i searched the other day.

What i have gone for is a spreadsheet that is similar in it's apperance to the average scorecard, but you just add new columns to so that you can look back at all your previous games, just like actually keeping the real scorecards

Once i get home tonight i'll post it up for anyone who wants it (and it won't be locked so others can play with it if they want).

Damian