Hole handicap

[cia2a629]

I am looking to have cards with each golfers handicap strokes per hole. the formula I have has a flaw I can't fix. I want to be able to enter the formula on different courses and only change the hole handicap per course.
=IF(B10<0,0,IF(B10<18,1,IF(B10<36,2,3)))-B8

Course Name:		PINE HOLLOW GOLF CLUB
Hole		1	2	3	4	5	6	7	8	9	Out
Gold 68.0/125		340	464	169	332	131	242	359	382	348	2767	No Stroke		o
Red 69.0/122		305	394	137	310	114	222	332	322	317	2453	1 Stroke
Hcp.		13	9	11	7	15	17	3	1	5		2 Strokes		.
Par		4	5	3	4	3	4	4	5	4	36
Jim Shue	Hndcp											Birdey		4
5	5	-3										Par		2
George Jones												Bogey		1
18	18											Dbl. Bogey		0
Nate Johnson												Trip. Bogey		-1
20	20	6
Jack Bean
29	29	6
TEAM TOTAL
TEAM POINTS

 

[Crystalyzer]

What is the flaw? How is each players handicap calculated per hole? It's hard to determine based on a flawed formula. If you could describe it I might be able to help.

 

[AhoyNC]

Maybe this to get strokes per hole. If you change hole handicap strokes will be adjusted.
Copy formula across & down as needed.

Book4
	A	B	C	D	E	F	G	H	I	J	K	L
1	Course Name:		PINE HOLLOW GOLF CLUB
2	Hole		1	2	3	4	5	6	7	8	9	Out
3	Gold 68.0/125		340	464	169	332	131	242	359	382	348	2767
4	Red 69.0/122		305	394	137	310	114	222	332	322	317	2453
5	Hcp.		13	9	11	7	15	17	3	1	5
6	Par		4	5	3	4	3	4	4	5	4	36
7	Jim Shue	Hndcp
8	5	5	0	0	0	0	0	0	1	1	1
9	George Jones
10	18	18	1	1	1	1	1	1	1	1	1
11	Nate Johnson
12	20	20	1	1	1	1	1	1	1	2	1
13	Jack Bean
14	29	29	1	2	2	2	1	1	2	2	2
Sheet1

Cell Formulas
Range	Formula
C8:K8,C14:K14,C12:K12,C10:K10	C8	=INT($B8/18)+IF(C$5<=MOD($B8,18),1,0)

 

[cia2a629]

Thank you so much ,this works perfectly

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[cia2a629]

For holes 10-18 what do I change>?

Hole		10	11	12	13	14	15	16	17	18
Gold		339	364	276	150	331	165	445	433	346
Red		294	337	268	125	287	136	399	399	250
Hcp.		12	8	18	16	2	10	14	6	4
Par		4	4	4	3	4	3	5	5	4
Jim Shue
5		C8:K8,C14:K14,C12:K12,C10:K10	0	0	0	0	0	0	0	0
George Jones
18		0	C8:K8,C14:K14,C12:K12,C10:K10	0	0	0	0	0	0	0
Nate Johnson
20		###	0	0	0	0	0	0	0	0
Jack Bean
29

 

[AhoyNC]

You shouldn't need to change anything.
This is the formula in cell M8 below.
INT($B8/18)+IF(M$5<=MOD($B8,18),1,0)
You just need to skip the OUT column in your worksheet (column L in the example below).

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
1	Course Name:		PINE HOLLOW GOLF CLUB
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18
3	Gold 68.0/125		340	464	169	332	131	242	359	382	348	2767
4	Red 69.0/122		305	394	137	310	114	222	332	322	317	2453
5	Hcp.		13	9	11	7	15	17	3	1	5		12	8	18	16	2	10	14	6	4
6	Par		4	5	3	4	3	4	4	5	4	36
7	Jim Shue	Hndcp
8	5	5	0	0	0	0	0	0	1	1	1		0	0	0	0	1	0	0	0	1
9	George Jones												0	0	0	0	0	0	0	0	0
10	18	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1
11	Nate Johnson												0	0	0	0	0	0	0	0	0
12	20	20	1	1	1	1	1	1	1	2	1		1	1	1	1	2	1	1	1	1
13	Jack Bean												0	0	0	0	0	0	0	0	0
14	29	29	1	2	2	2	1	1	2	2	2		1	2	1	1	2	2	1	2	2
Sheet1

Cell Formulas
Range	Formula
C8:K8,C14:K14,C12:K12,C10:K10,M8:U14	C8	=INT($B8/18)+IF(C$5<=MOD($B8,18),1,0)

 

[cia2a629]

Ok, Thank you again, Do I need to enter the RANGE information anywhere on the sheet.?

 

[AhoyNC]

Don't enter the range info. That's just the XL2BB program showing the range where the formula is entered.
Just enter the formula in the first cell and copy it across and down as needed. You will need to skip or delete the formula from your OUT column. Also, if you are skipping a row between names you will need to skip those rows or go back and delete the formula from those rows.

 

[cia2a629]

Ok, now I get it, and for different courses I change only the column and rows that show the hole handicaps. Correct