You are here:

Excel/System 36 golf scoring excel formula

Advertisement


Question
could you help me with the formula for golf scoring using System 36

Answer
How to use System36Hdcp UDF
How to use System36Hdc  
Hi Lito,

I'm assuming you would like a formula that will calculate a golfer's System 36 handicap based on their scoring for an 18-hole round. Here is a user-defined function (UDF) that should make this easy.  It assumes you have the hole-by-hole scores on your worksheet, along with the hole-by-hole par values.

Use the function as shown the example given in the comments in the code for the function System36Hdcp below:

Function System36Hdcp(Par1to9 As Range, Par10to18 As Range, _
         Score1to9 As Range, Score10to18 As Range) As Integer
'Calculates golfer's System 36 18-hole handicap based on Par and Score data
'for the round of golf.
'Inputs:
'  Par1to9     Range containing Par values for holes 1-9
'  Par10to18   Range containing Par values for holes 10-18
'  Score1to9   Range containing golfer's scores for holes 1-9
'  Score10to18 Range containing golfer's scores for holes 10-18

'Example:
'  =System36Hdcp(B2:J2,L2:T2,B4:J4,L4:T4)
'     will yield the golfer's handicap assuming that his/her scores
'     for the front nine are in range B4:J4 and for the back nine
'     are in range L4:T4. Ranges B2:J2 and L2:T2 contain the Par values
'     for the front and back nine, respectively.
'
Dim iHole          As Integer
Dim ParDif          As Integer
Dim System36Points   As Integer

System36Points = 0

'Figure points on front nine
For iHole = 1 To 9
  ParDif = Score1to9(iHole) - Par1to9(iHole)
  If ParDif <= 0 Then
     System36Points = System36Points + 2
  ElseIf ParDif = 1 Then
     System36Points = System36Points + 1
  End If
Next iHole

'Figure points on back nine
For iHole = 9 To 18
  ParDif = Score10to18(iHole) - Par10to18(iHole)
  If ParDif <= 0 Then
     System36Points = System36Points + 2
  ElseIf ParDif = 1 Then
     System36Points = System36Points + 1
  End If
Next iHole

'Subtract points from 36 to get System 36 handicap
  System36Hdcp = 36 - System36Points
End Function
______________________________________________________________________________

To install this function in your workbook go to the Visual Basic Editor (keyboard ALT-TMV in Excel), then insert a new macro module (Alt-IM), and finally paste the above code into the Code pane.

I have attached an image of an example worksheet showing the formula in cell W2 yielding for the round a 4 handicap.

I hope I have interpreted your question correctly, but if not please feel free to follow up and I will be happy to update my response.

Damon  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.