# Golf handicap score

#### Rock5150

##### New Member
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fluff

##### MrExcel MVP, Moderator
If you put the lookup to get the handicap in (say col T) you could use
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRST
1NameHole 1Hole 2Hole 3Hole 4Hole 5Hole 6Hole 7Hole 8Hole 9Hole 10Hole 11Hole 12Hole 13Hole 14Hole 15Hole 16Hole 17Hole 18
2Handicap131579210111217183468141516
3
4John Doe score454436455844325556
5John Doe Net4433354558432145568
6
7James Smith score454436455844325556
8James Smith Net33232434473210444524
9
10Tony Jones score454436455844325556
11Tony Jones Net4444354558443255562
Main
Cell Formulas
RangeFormula
B5:S5,B11:S11,B8:S8B5=B4-(B\$2<=MOD(\$T5,18))-INT(\$T5/18)

#### Rock5150

##### New Member
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

##### MrExcel MVP, Moderator
As that's a totally different question, it needs a new thread. Thanks

#### Rock5150

##### New Member

As that's a totally different question, it needs a new thread. Thanks
Ok I created a new threas

#### Rock5150

##### New Member

ABCDEFGHIJKLMNOPQRSTU
1NameHole 1Hole 2Hole 3Hole 4Hole 5Hole 6Hole 7Hole 8Hole 9Hole 10Hole 11Hole 12Hole 13Hole 14Hole 15Hole 16Hole 17Hole 18HandicapTotal
2Handicap131579210111217183468141516
3
4John Doe45443645584434555684
5John Doe Net443335455843234556876
6
7James Smith45443645584434555684
8James Smith Net2222132336210233344044
9
10Tony Jones45443645584464555687
11Tony Jones Net3433253448435344451671
12
13Dave Johnson45443645584433555683
14Dave Johnson Net3433253448432245561370
Scores
Cell Formulas
RangeFormula
U13:U14,U10:U11,U7:U8,U4:U5U4=SUM(B4:S4)
B5:S5,B14:S14,B11:S11,B8:S8B5=B4-(B\$2<=MOD(\$T5,18))-INT(\$T5/18)
T5,T14,T11,T8T5=VLOOKUP(A4,Handicap!A:B,2,FALSE)

#### Rock5150

##### New Member
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.

ABCDE
1James SmithJohn DoeTony JonesDave Johnson
2James Smith
3John Doe
4Tony Jones
5Dave Johnson
Grid

#### Fluff

##### MrExcel MVP, Moderator
You have posted these to the wrong thread. You need to post them to your new thread.

Replies
8
Views
775
Replies
6
Views
168
Replies
6
Views
175
Replies
10
Views
367
Replies
7
Views
197

1,130,125
Messages
5,640,261
Members
417,132
Latest member
Srw123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back