Hadicapped gold scorecard help

[mikewohlwend]

I am making a scorecard for a golf trip that and want to make it fill itself out. I have some guys that have handicaps over 18 and they need to get more than 1 stroke per hole, but they can't get more than 1 on a par 3. The strokes are based on the holes handicaps from smallest to largest.

I have been able to figure out how to get it to auto populate for someone that's 0-18, I just can't figure out the multiple strokes per hole with over 18.

I don't know how to upload what I have so that I can get some suggestions.

 

[mikewohlwend]

Unless you have a golfer with a 33 or higher handicap you should not see more than 1 stroke on a par 3. Most par 3's have the higher handicap ratings (at least on the courses I've played).

We do cap the handicaps at 31 or 32 depending on the 18 you play.

 

[AhoyNC]

Try this, it should cap all the par 3's at a max of 1 stroke.
IF(AND(C$4=3,INT($B6/18)+IF(C$3<=MOD($B6,18),2,1)>1),"*",CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**"))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	5	8	11	9	17	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	*		*					*							*				*
7
8	Golfer3	12	*		*	*	*	*		*	*		*	*			*	*	*		*
9
10	Golfer4	24	**	*	*	*	*	*	*	**	*	**	*	*	*	*	**	*	**	*	**
11
12	Golfer5	32	**	**	*	**	**	**	*	**	**	**	**	**	*	**	**	**	**	*	**
Sheet1

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K6,C12:U12,C8:C11,D10:U10,M8:U8,D8:K8,M6:U6	C6	=IF(AND(C$4=3,INT($B6/18)+IF(C$3<=MOD($B6,18),2,1)>1),"*",CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**"))

 

[mikewohlwend]

Try this, it should cap all the par 3's at a max of 1 stroke.
IF(AND(C$4=3,INT($B6/18)+IF(C$3<=MOD($B6,18),2,1)>1),"*",CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**"))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	5	8	11	9	17	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	*		*					*							*				*
7
8	Golfer3	12	*		*	*	*	*		*	*		*	*			*	*	*		*
9
10	Golfer4	24	**	*	*	*	*	*	*	**	*	**	*	*	*	*	**	*	**	*	**
11
12	Golfer5	32	**	**	*	**	**	**	*	**	**	**	**	**	*	**	**	**	**	*	**
Sheet1

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K6,C12:U12,C8:C11,D10:U10,M8:U8,D8:K8,M6:U6	C6	=IF(AND(C$4=3,INT($B6/18)+IF(C$3<=MOD($B6,18),2,1)>1),"*",CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**"))

Thank you for the help.

Your example is off you have 2 holes with an 8 hdcp and no 15 hdcp.

That gets me closer but it's still only 29 strokes. There is 3 holes off. Holes 3,11 & 17 should all get 2 strokes.

Once I get above 21 it is always 1 stroke short. Having a par 3 as the 4th hardest hole on the course throws it for a loop. Is there any way you can change you sample to the same par and hdcp as mine and see if it works for you?

 

[awoohaw]

What do you do for a 15 handicap on a course with 4 par 3s? Does the person get 2 strokes on the #1 handicap hole (if the #1 HCP hole is not a par 3)?

 

[mikewohlwend]

What do you do for a 15 handicap on a course with 4 par 3s? Does the person get 2 strokes on the #1 handicap hole (if the #1 HCP hole is not a par 3)?

If the person is a 15 they would get 1 stroke on the hardest 15 holes. You can get 1 stroke on a par 3 you just can't get 2 strokes.

 

[AhoyNC]

I'm still looking at it, but I'm not sure you can do this with a formula.
It's easy to limit a par 3 to a max of 1 stroke, but if they get 2 stokes on a par 3 you need to move those extra strokes to higher handicap holes that also aren't par 3's.

Based on your score card above any gofer with a handicap of 22 or above will get 2 strokes on some par 3's.
So in the example below we would need to move 3 stokes for golfer6 to higher handicap holes.

Golf habdicap per hole.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		11	7	15	1	9	3	17	13	5		2	18	8	12	14	10	4	16	6
4	Par		5	4	4	4	3	4	3	5	4	36	4	4	5	4	3	4	3	4	5	36	72
5	Name	Hndcp
6	Golfer2	5	0	0	0	1	0	1	0	0	1		1	0	0	0	0	0	1	0	0		5
7	Golfer3	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1		18
8	Golfer4	22	1	1	1	2	1	2	1	1	1		2	1	1	1	1	1	2	1	1		22
9	Golfer5	28	1	2	1	2	2	2	1	1	2		2	1	2	1	1	2	2	1	2		28
10	Golfer6	32	2	2	1	2	2	2	1	2	2		2	1	2	2	2	2	2	1	2		32
Sheet3

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K10,M6:U10	C6	=INT($B6/18)+IF(C$3<=MOD($B6,18),1,0)
W6:W10	W6	=SUM(C6:U6)

 

[AhoyNC]

I haven't had a lot of time to work with this, but in the example below I was thinking maybe you could set up a Par 3 table like in rows 15 to 30.
The table is dynamic and numbers will change if you change courses.
I just used the number of strokes instead of asterisks in the example below.

Rows 16 & 17 just list the par 3's sorted from the most difficult.
Rows 19 & 20 show if you have a handicap from 22 to 26 you need to add 1 stroke to a non-par3. A handicap from 27 to 31 you need to add 2 strokes, etc.

Row 23 just lists handicaps from 22 to 32.
Row 24 I just calculated the next highest handicap hole you want to add the stroke too. So, for a 22 hdcp you would look for the hole with a 5 hdcp which i hole 9 in this example. for a 24 hdcp you would add 1 to hole 2. You will notice that if you have a 26 hdcp it shows hole 5, but hole 5 is a par 3, if you look over 1 column you would look for the 10 hdcp hole which is hole 15.
Since handicaps 27 and higher need a 2nd stroke added Row 30 shows what hole to add it too. Since a 30 hdcp shows the 2nd stroke needs to be added to hole 14 which is a par 3 you need to look over one column and see hole 3. So with a 30 hdcp you would add 1 stroke to hole 8 and 1 stroke to hole 3.

This was done quickly so the table in not set up as a lookup table, but maybe something like this could be done as a VLOOK table to use in a formula to automatically add the strokes (not sure).

Unless you just have a large number of golfers the easiest way is probably to use the original formula and then using the Table just manually change the the handicaps of 22 and above.

Golf habdicapfor MSG.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		11	7	15	1	9	3	17	13	5		2	18	8	12	14	10	4	16	6
4	Par		5	4	4	4	3	4	3	5	4	36	4	4	5	4	3	4	3	4	5	36	72
5	Name	Hndcp
6	Golfer2	5	0	0	0	1	0	1	0	0	1		1	0	0	0	0	0	1	0	0
7	Golfer3	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1
8	Golfer4	22	1	1	1	2	1	2	1	1	1		2	1	1	1	1	1	2	1	1
9	Golfer5	28	1	2	1	2	2	2	1	1	2		2	1	2	1	1	2	2	1	2
10	Golfer6	32	2	2	1	2	2	2	1	2	2		2	1	2	2	2	2	2	1	2
11	Golfer7	27	1	2	1	2	2	2	1	1	2		2	1	2	1	1	1	2	1	2
12	Golfer8	26	1	2	1	2	1	2	1	1	2		2	1	2	1	1	1	2	1	2
13
14
15	Par 3 Table
16	Hole #	16	5	14	7
17	Hole Hdcp	4	9	14	17
18
19	Golfer Hdcp	22	27	32	35
20	Strokes	1	2	3	4
21
22
23	Golfer Hdcp	22	23	24	25	26	27	28	29	30	31	32
24	Hole Hdcp	5	6	7	8	9	10	11	12	13	14	15
25	Move to Hole	9	18	2	12	5	15	1	13	8	14	3
26	Is it a Par 3	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE
27
28	2 nd Stroke
29	Hole Hdcp						11	12	13	14	15	16
30	Move to Hole						1	13	8	14	3	17
Sheet4

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K12,M6:U12	C6	=INT($B6/18)+IF(C$3<=MOD($B6,18),1,0)
B16:E17	B16	=SORT(FILTER(C2:U3,C4:U4=3),2,1,-1)
B19:E19	B19	=B17+18
C20:E20	C20	=IF(C19<>"",B20+1)
B24:L24	B24	=18-(36-B23)+1
B25:L25,G30:L30	B25	=FILTER($C$2:$U$2,$C$3:$U$3=B24)
B26:L26	B26	=FILTER($C$4:$U$4,B25=$C$2:$U$2)=3
Dynamic array formulas.

 

[mikewohlwend]

I haven't had a lot of time to work with this, but in the example below I was thinking maybe you could set up a Par 3 table like in rows 15 to 30.
The table is dynamic and numbers will change if you change courses.
I just used the number of strokes instead of asterisks in the example below.

Rows 16 & 17 just list the par 3's sorted from the most difficult.
Rows 19 & 20 show if you have a handicap from 22 to 26 you need to add 1 stroke to a non-par3. A handicap from 27 to 31 you need to add 2 strokes, etc.

Row 23 just lists handicaps from 22 to 32.
Row 24 I just calculated the next highest handicap hole you want to add the stroke too. So, for a 22 hdcp you would look for the hole with a 5 hdcp which i hole 9 in this example. for a 24 hdcp you would add 1 to hole 2. You will notice that if you have a 26 hdcp it shows hole 5, but hole 5 is a par 3, if you look over 1 column you would look for the 10 hdcp hole which is hole 15.
Since handicaps 27 and higher need a 2nd stroke added Row 30 shows what hole to add it too. Since a 30 hdcp shows the 2nd stroke needs to be added to hole 14 which is a par 3 you need to look over one column and see hole 3. So with a 30 hdcp you would add 1 stroke to hole 8 and 1 stroke to hole 3.

This was done quickly so the table in not set up as a lookup table, but maybe something like this could be done as a VLOOK table to use in a formula to automatically add the strokes (not sure).

Unless you just have a large number of golfers the easiest way is probably to use the original formula and then using the Table just manually change the the handicaps of 22 and above.

Golf habdicapfor MSG.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		11	7	15	1	9	3	17	13	5		2	18	8	12	14	10	4	16	6
4	Par		5	4	4	4	3	4	3	5	4	36	4	4	5	4	3	4	3	4	5	36	72
5	Name	Hndcp
6	Golfer2	5	0	0	0	1	0	1	0	0	1		1	0	0	0	0	0	1	0	0
7	Golfer3	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1
8	Golfer4	22	1	1	1	2	1	2	1	1	1		2	1	1	1	1	1	2	1	1
9	Golfer5	28	1	2	1	2	2	2	1	1	2		2	1	2	1	1	2	2	1	2
10	Golfer6	32	2	2	1	2	2	2	1	2	2		2	1	2	2	2	2	2	1	2
11	Golfer7	27	1	2	1	2	2	2	1	1	2		2	1	2	1	1	1	2	1	2
12	Golfer8	26	1	2	1	2	1	2	1	1	2		2	1	2	1	1	1	2	1	2
13
14
15	Par 3 Table
16	Hole #	16	5	14	7
17	Hole Hdcp	4	9	14	17
18
19	Golfer Hdcp	22	27	32	35
20	Strokes	1	2	3	4
21
22
23	Golfer Hdcp	22	23	24	25	26	27	28	29	30	31	32
24	Hole Hdcp	5	6	7	8	9	10	11	12	13	14	15
25	Move to Hole	9	18	2	12	5	15	1	13	8	14	3
26	Is it a Par 3	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE
27
28	2 nd Stroke
29	Hole Hdcp						11	12	13	14	15	16
30	Move to Hole						1	13	8	14	3	17
Sheet4

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K12,M6:U12	C6	=INT($B6/18)+IF(C$3<=MOD($B6,18),1,0)
B16:E17	B16	=SORT(FILTER(C2:U3,C4:U4=3),2,1,-1)
B19:E19	B19	=B17+18
C20:E20	C20	=IF(C19<>"",B20+1)
B24:L24	B24	=18-(36-B23)+1
B25:L25,G30:L30	B25	=FILTER($C$2:$U$2,$C$3:$U$3=B24)
B26:L26	B26	=FILTER($C$4:$U$4,B25=$C$2:$U$2)=3
Dynamic array formulas.

I ended up making a table outside the border and used conditional formatting to over ride what gets typed in the player handicap. I’ll send a screenshot of that tomorrow. It’s not perfect but it does whets needed

 

[AhoyNC]

Good to hear you got it worked out.

 

[mikewohlwend]

Good to hear you got it worked out.

Thank you again for all the help.