Golf handicap score

[Rock5150]

Hello

I want to create a spreadsheet where i have a player listed in Column A, then Column B-S would have the "calculated" net score. So if the handicap is an 8, then the person gets a score of one stroke off the hardest 8 holes (1-8). See row 2 of attached.

If the handicap is a 24, then the golfer gets a score one off each score and then "two strokes " off handicap holes 1-6 as it would then "loop" back. Holes 1-6 person gets "two" strokes off, 7-18 one stroke

I have sheet 2 that lists the name and handicap. So if they see the name in column A, the vlookup the handicap and then calculate. So assume John Doe handicap is 8, then on hole 2, his net score would be "4". On

Name

Hole 1

Hole 2

Hole 3

Hole 4

Hole 5

Hole 6

Hole 7

Hole 8

Hole 9

Hole 10

Hole 11

Hole 12

Hole 13

Hole 14

Hole 15

Hole 16

Hole 17

Hole 18

Handicap

13​

1​

5​

7​

9​

2​

10​

11​

12​

17​

18​

3​

4​

6​

8​

14​

15​

16​

John Doe score

4​

5​

4​

4​

3​

6​

4​

5​

5​

8​

4​

4​

3​

2​

5​

5​

5​

6​

John Doe Net

James Smith score

4​

5​

4​

4​

3​

6​

4​

5​

5​

8​

4​

4​

3​

2​

5​

5​

5​

6​

James Smith Net

Tony Jones score

4​

5​

4​

4​

3​

6​

4​

5​

5​

8​

4​

4​

3​

2​

5​

5​

5​

6​

Tony Jones Net

 

[Fluff]

If you put the lookup to get the handicap in (say col T) you could use

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Hole 1	Hole 2	Hole 3	Hole 4	Hole 5	Hole 6	Hole 7	Hole 8	Hole 9	Hole 10	Hole 11	Hole 12	Hole 13	Hole 14	Hole 15	Hole 16	Hole 17	Hole 18
2	Handicap	13	1	5	7	9	2	10	11	12	17	18	3	4	6	8	14	15	16
3
4	John Doe score	4	5	4	4	3	6	4	5	5	8	4	4	3	2	5	5	5	6
5	John Doe Net	4	4	3	3	3	5	4	5	5	8	4	3	2	1	4	5	5	6	8
6
7	James Smith score	4	5	4	4	3	6	4	5	5	8	4	4	3	2	5	5	5	6
8	James Smith Net	3	3	2	3	2	4	3	4	4	7	3	2	1	0	4	4	4	5	24
9
10	Tony Jones score	4	5	4	4	3	6	4	5	5	8	4	4	3	2	5	5	5	6
11	Tony Jones Net	4	4	4	4	3	5	4	5	5	8	4	4	3	2	5	5	5	6	2
Main

Cell Formulas
Range	Formula
B5:S5,B11:S11,B8:S8	B5	=B4-(B$2<=MOD($T5,18))-INT($T5/18)

 

[Rock5150]

Thanks Fluff. Ok so now i have another part of the spreadsheet. Our league will have "pairings" so that the "pair" with the lowest combined net score wins. So what i did was use the above you provided and then use a MIN function to take B5 and B8 and populate B9 with the lower score, then extrapolate to Column S and then put the net total (min score per hole) in column U.

Do you know of a formula where i can put the total net score in the cells below? For example, Jon and Tony had a combined net score of 68 (took min of each hole score between the two players using MIN function)

I think for this what i am looking for is to calculate the low net scores for each pair somehow by looking at their individual scores so i don't have to type each pair one by one on a separate spreadsheet. In this case, manually, i would have to type john and james, tony and James, dave and james with their scores copied each time.

Maybe a vlookup of each person's score and somehow calculate the MIN for each hole between the two and have it somehow add up

Column A	James Smith	John Doe	Tony Jones	Dave Johnson
James Smith	(would be blank)
John Doe		blocked
Tony Jones			Blocked
Dave Johnson				Blocked

 

[Fluff]

As that's a totally different question, it needs a new thread. Thanks

 

[Rock5150]

As that's a totally different question, it needs a new thread. Thanks

Ok I created a new threas

 

[Fluff]

 

[Rock5150]

Here is the spreadsheet. I use Excel 365 (2021 downloaded)

Ham and Egg spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Name	Hole 1	Hole 2	Hole 3	Hole 4	Hole 5	Hole 6	Hole 7	Hole 8	Hole 9	Hole 10	Hole 11	Hole 12	Hole 13	Hole 14	Hole 15	Hole 16	Hole 17	Hole 18	Handicap	Total
2	Handicap	13	1	5	7	9	2	10	11	12	17	18	3	4	6	8	14	15	16
3
4	John Doe	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84
5	John Doe Net	4	4	3	3	3	5	4	5	5	8	4	3	2	3	4	5	5	6	8	76
6
7	James Smith	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84
8	James Smith Net	2	2	2	2	1	3	2	3	3	6	2	1	0	2	3	3	3	4	40	44
9
10	Tony Jones	4	5	4	4	3	6	4	5	5	8	4	4	6	4	5	5	5	6		87
11	Tony Jones Net	3	4	3	3	2	5	3	4	4	8	4	3	5	3	4	4	4	5	16	71
12
13	Dave Johnson	4	5	4	4	3	6	4	5	5	8	4	4	3	3	5	5	5	6		83
14	Dave Johnson Net	3	4	3	3	2	5	3	4	4	8	4	3	2	2	4	5	5	6	13	70
Scores

Cell Formulas
Range	Formula
U13:U14,U10:U11,U7:U8,U4:U5	U4	=SUM(B4:S4)
B5:S5,B14:S14,B11:S11,B8:S8	B5	=B4-(B$2<=MOD($T5,18))-INT($T5/18)
T5,T14,T11,T8	T5	=VLOOKUP(A4,Handicap!A:B,2,FALSE)

 

[Rock5150]

Here is the second spreadsheet in the desired format that i need the final net score combined taking the lowest score of the two per hole.

Ham and Egg spreadsheet.xlsx
	A	B	C	D	E
1		James Smith	John Doe	Tony Jones	Dave Johnson
2	James Smith
3	John Doe
4	Tony Jones
5	Dave Johnson
Grid

 

[Fluff]

You have posted these to the wrong thread. You need to post them to your new thread.

 

[Rock5150]

Thanks Fluff. Here is the new thread Need to populate table with golf scores