Golf handicap score

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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

NameHole 1Hole 2Hole 3Hole 4Hole 5Hole 6Hole 7Hole 8Hole 9Hole 10Hole 11Hole 12Hole 13Hole 14Hole 15Hole 16Hole 17Hole 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
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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 AJames SmithJohn DoeTony JonesDave Johnson
James Smith(would be blank)
John Doeblocked
Tony JonesBlocked
Dave JohnsonBlocked
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
As that's a totally different question, it needs a new thread. Thanks
 

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Ham and Egg spreadsheet.xlsx
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
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1James SmithJohn DoeTony JonesDave Johnson
2James Smith
3John Doe
4Tony Jones
5Dave Johnson
Grid
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
You have posted these to the wrong thread. You need to post them to your new thread. :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top