IF Statement for Golf Scores

[Guest]

Hi Everyone

For all you golfers out there.....I am trying to create a spreadsheet and
need some help converting a score into stableford points.

My Handicap is 8 so i get a shot on the 8 hardest holes
Hole 1 = Par 5 and is the 8th Hardest hole
If i score 3 on hole 1 - 1 shot = 2 therfore = 5 Points
If i score 4 on hole 1 - 1 shot = 3 therfore = 4 Points
If i score 5 on hole 1 - 1 shot = 4 therfore = 3 Points
If i score 6 on hole 1 - 1 shot = 5 therfore = 2 Points
If i score 7 on hole 1 - 1 shot = 6 therfore = 1 Points
If i score 8+ on hole 1 - 1 shot = 7+ therfore = 0 Points

I Hope this makes sense, you probably need to be a golfer to understand or
maybe not?

Any help will be great

 

[Ian]

I'm not a golfer, but it appears to me that score + points =8.
Assuming your score is in A1:

=IF(OR(A1=0,8-A1<0),0,8-A1)

This will enter 0 if the no score (or zero) is entered, or if A1 is greater
than 8, otherwise it will enter 8 - A1

 

[Guest]

Try this:

assuming hole 1 is in A1

=LOOKUP(A1-1,{2,3,4,5,6,7},{5,4,3,2,1,0})

HTH
JG

 

[daddylonglegs]

=if(a1="","",max(0,8-a1)

 

[Guest]

Thanks to you all for your quick responses although i am not sure they have
answered my question??

Maybe i could forward my spreadsheet to someone as it may be easier to
understand my question with all the data?

Is anyone happy for me to do this? If so can i have your mail address?

Thanks
Dave

 

[Guest]

Ignore my last thread, it works great after i have played with it a little

Thanks everyone
Dave

 

[Ron Rosenfeld]

Hi Everyone

For all you golfers out there.....I am trying to create a spreadsheet and
need some help converting a score into stableford points.

My Handicap is 8 so i get a shot on the 8 hardest holes
Hole 1 = Par 5 and is the 8th Hardest hole
If i score 3 on hole 1 - 1 shot = 2 therfore = 5 Points
If i score 4 on hole 1 - 1 shot = 3 therfore = 4 Points
If i score 5 on hole 1 - 1 shot = 4 therfore = 3 Points
If i score 6 on hole 1 - 1 shot = 5 therfore = 2 Points
If i score 7 on hole 1 - 1 shot = 6 therfore = 1 Points
If i score 8+ on hole 1 - 1 shot = 7+ therfore = 0 Points

I Hope this makes sense, you probably need to be a golfer to understand or
maybe not?

Any help will be great

Here's a User Defined Function that should compute the Stableford Points for
each hole, given the handicap, score, stroke index and par.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter the formula:

=StablefordPoints(Handicap, Score, SI, Par)

into some cell. The variables can be entered directly or, more simply, as cell
references or named cells.

Let me know if this does what you want.

=================================================
Option Explicit

Function StablefordPoints(Handicap, Score, SI, Par)
'Stableford Points Calculation
Dim Count, SP

If Handicap >= 0 Then
If Score > 0 Then
'Work out how many points the golfer receives
SP = 0
If Handicap = 0 Then
SP = Score
ElseIf Handicap <= 18 Then
For Count = 1 To Handicap
If SI = Count Then
SP = Score - 1
Exit For
Else
SP = Score
End If
Next
ElseIf Handicap >= 19 And Handicap <= 36 Then
For Count = 1 To Handicap - 18
If SI = Count Then
SP = Score - 2
Exit For
Else
SP = Score - 1
End If
Next
Else 'Handicap 37 to 45
For Count = 1 To Handicap - 36
If SI = Count Then
SP = Score - 3
Exit For
Else
SP = Score - 2
End If
Next
End If 'Checking Golfers Handicap

'Calculate the Stableford Points
If SP - 1 = Par Then
StablefordPoints = 1
ElseIf SP = Par Then
StablefordPoints = 2
ElseIf SP + 1 = Par Then
StablefordPoints = 3
ElseIf SP + 2 = Par Then
StablefordPoints = 4
ElseIf SP + 3 = Par Then
StablefordPoints = 5
ElseIf SP + 4 = Par Then
StablefordPoints = 6
ElseIf SP + 5 = Par Then
StablefordPoints = 7
ElseIf SP + 6 = Par Then
StablefordPoints = 8
End If 'Calculating Stableford Points
End If 'Score <> sEmpty
End If
End Function
======================================

--ron

 

[Guest]

Thanks Ron, that wroks great aswell.

Here's a User Defined Function that should compute the Stableford Points for
each hole, given the handicap, score, stroke index and par.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter the formula:

=StablefordPoints(Handicap, Score, SI, Par)

into some cell. The variables can be entered directly or, more simply, as cell
references or named cells.

Let me know if this does what you want.

=================================================
Option Explicit

Function StablefordPoints(Handicap, Score, SI, Par)
'Stableford Points Calculation
Dim Count, SP

If Handicap >= 0 Then
If Score > 0 Then
'Work out how many points the golfer receives
SP = 0
If Handicap = 0 Then
SP = Score
ElseIf Handicap <= 18 Then
For Count = 1 To Handicap
If SI = Count Then
SP = Score - 1
Exit For
Else
SP = Score
End If
Next
ElseIf Handicap >= 19 And Handicap <= 36 Then
For Count = 1 To Handicap - 18
If SI = Count Then
SP = Score - 2
Exit For
Else
SP = Score - 1
End If
Next
Else 'Handicap 37 to 45
For Count = 1 To Handicap - 36
If SI = Count Then
SP = Score - 3
Exit For
Else
SP = Score - 2
End If
Next
End If 'Checking Golfers Handicap

'Calculate the Stableford Points
If SP - 1 = Par Then
StablefordPoints = 1
ElseIf SP = Par Then
StablefordPoints = 2
ElseIf SP + 1 = Par Then
StablefordPoints = 3
ElseIf SP + 2 = Par Then
StablefordPoints = 4
ElseIf SP + 3 = Par Then
StablefordPoints = 5
ElseIf SP + 4 = Par Then
StablefordPoints = 6
ElseIf SP + 5 = Par Then
StablefordPoints = 7
ElseIf SP + 6 = Par Then
StablefordPoints = 8
End If 'Calculating Stableford Points
End If 'Score <> sEmpty
End If
End Function
======================================

--ron

 

[Ron Rosenfeld]

Thanks Ron, that wroks great aswell.

Glad to help.
--ron