software

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 greatI'm not a golfer, but it appears to me that score points =8.
Assuming your score is in A1:

=IF(OR(A1=0,8-A1lt;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
--
Ian
--
quot;Davaquot; gt; wrote in message
...
gt; Hi Everyone
gt;
gt; For all you golfers out there.....I am trying to create a spreadsheet and
gt; need some help converting a score into stableford points.
gt;
gt; My Handicap is 8 so i get a shot on the 8 hardest holes
gt; Hole 1 = Par 5 and is the 8th Hardest hole
gt; If i score 3 on hole 1 - 1 shot = 2 therfore = 5 Points
gt; If i score 4 on hole 1 - 1 shot = 3 therfore = 4 Points
gt; If i score 5 on hole 1 - 1 shot = 4 therfore = 3 Points
gt; If i score 6 on hole 1 - 1 shot = 5 therfore = 2 Points
gt; If i score 7 on hole 1 - 1 shot = 6 therfore = 1 Points
gt; If i score 8 on hole 1 - 1 shot = 7 therfore = 0 Points
gt;
gt; I Hope this makes sense, you probably need to be a golfer to understand or
gt; maybe not?
gt;
gt; Any help will be great
gt;
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

quot;Davaquot; wrote:

gt; Hi Everyone
gt;
gt; For all you golfers out there.....I am trying to create a spreadsheet and
gt; need some help converting a score into stableford points.
gt;
gt; My Handicap is 8 so i get a shot on the 8 hardest holes
gt; Hole 1 = Par 5 and is the 8th Hardest hole
gt; If i score 3 on hole 1 - 1 shot = 2 therfore = 5 Points
gt; If i score 4 on hole 1 - 1 shot = 3 therfore = 4 Points
gt; If i score 5 on hole 1 - 1 shot = 4 therfore = 3 Points
gt; If i score 6 on hole 1 - 1 shot = 5 therfore = 2 Points
gt; If i score 7 on hole 1 - 1 shot = 6 therfore = 1 Points
gt; If i score 8 on hole 1 - 1 shot = 7 therfore = 0 Points
gt;
gt; I Hope this makes sense, you probably need to be a golfer to understand or
gt; maybe not?
gt;
gt; Any help will be great
gt;


=if(a1=quot;quot;,quot;quot;,max(0,8-a1))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=506306Thanks 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

quot;daddylonglegsquot; wrote:

gt;
gt; =if(a1=quot;quot;,quot;quot;,max(0,8-a1))
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=506306
gt;
gt;

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

Thanks everyone
Dave

quot;Davaquot; wrote:

gt; Thanks to you all for your quick responses although i am not sure they have
gt; answered my question??
gt;
gt; Maybe i could forward my spreadsheet to someone as it may be easier to
gt; understand my question with all the data?
gt;
gt; Is anyone happy for me to do this? If so can i have your mail address?
gt;
gt; Thanks
gt; Dave
gt;
gt; quot;daddylonglegsquot; wrote:
gt;
gt; gt;
gt; gt; =if(a1=quot;quot;,quot;quot;,max(0,8-a1))
gt; gt;
gt; gt;
gt; gt; --
gt; gt; daddylonglegs
gt; gt; ------------------------------------------------------------------------
gt; gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=506306
gt; gt;
gt; gt;

On Mon, 30 Jan 2006 03:50:27 -0800, quot;Davaquot; gt;
wrote:

gt;Hi Everyone
gt;
gt;For all you golfers out there.....I am trying to create a spreadsheet and
gt;need some help converting a score into stableford points.
gt;
gt;My Handicap is 8 so i get a shot on the 8 hardest holes
gt;Hole 1 = Par 5 and is the 8th Hardest hole
gt;If i score 3 on hole 1 - 1 shot = 2 therfore = 5 Points
gt;If i score 4 on hole 1 - 1 shot = 3 therfore = 4 Points
gt;If i score 5 on hole 1 - 1 shot = 4 therfore = 3 Points
gt;If i score 6 on hole 1 - 1 shot = 5 therfore = 2 Points
gt;If i score 7 on hole 1 - 1 shot = 6 therfore = 1 Points
gt;If i score 8 on hole 1 - 1 shot = 7 therfore = 0 Points
gt;
gt;I Hope this makes sense, you probably need to be a golfer to understand or
gt;maybe not?
gt;
gt;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, lt;alt-F11gt; 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 gt;= 0 Then
If Score gt; 0 Then
'Work out how many points the golfer receives
SP = 0
If Handicap = 0 Then
SP = Score
ElseIf Handicap lt;= 18 Then
For Count = 1 To Handicap
If SI = Count Then
SP = Score - 1
Exit For
Else
SP = Score
End If
Next
ElseIf Handicap gt;= 19 And Handicap lt;= 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 lt;gt; sEmpty
End If
End Function
======================================

--ron

Thanks Ron, that wroks great aswell.

quot;Ron Rosenfeldquot; wrote:

gt; On Mon, 30 Jan 2006 03:50:27 -0800, quot;Davaquot; gt;
gt; wrote:
gt;
gt; gt;Hi Everyone
gt; gt;
gt; gt;For all you golfers out there.....I am trying to create a spreadsheet and
gt; gt;need some help converting a score into stableford points.
gt; gt;
gt; gt;My Handicap is 8 so i get a shot on the 8 hardest holes
gt; gt;Hole 1 = Par 5 and is the 8th Hardest hole
gt; gt;If i score 3 on hole 1 - 1 shot = 2 therfore = 5 Points
gt; gt;If i score 4 on hole 1 - 1 shot = 3 therfore = 4 Points
gt; gt;If i score 5 on hole 1 - 1 shot = 4 therfore = 3 Points
gt; gt;If i score 6 on hole 1 - 1 shot = 5 therfore = 2 Points
gt; gt;If i score 7 on hole 1 - 1 shot = 6 therfore = 1 Points
gt; gt;If i score 8 on hole 1 - 1 shot = 7 therfore = 0 Points
gt; gt;
gt; gt;I Hope this makes sense, you probably need to be a golfer to understand or
gt; gt;maybe not?
gt; gt;
gt; gt;Any help will be great
gt;
gt; Here's a User Defined Function that should compute the Stableford Points for
gt; each hole, given the handicap, score, stroke index and par.
gt;
gt; To enter this, lt;alt-F11gt; opens the VB Editor. Ensure your project is
gt; highlighted in the project explorer window, then Insert/Module and paste the
gt; code below into the window that opens.
gt;
gt; To use this, enter the formula:
gt;
gt; =StablefordPoints(Handicap, Score, SI, Par)
gt;
gt; into some cell. The variables can be entered directly or, more simply, as cell
gt; references or named cells.
gt;
gt; Let me know if this does what you want.
gt;
gt; =================================================
gt; Option Explicit
gt;
gt; Function StablefordPoints(Handicap, Score, SI, Par)
gt; 'Stableford Points Calculation
gt; Dim Count, SP
gt;
gt; If Handicap gt;= 0 Then
gt; If Score gt; 0 Then
gt; 'Work out how many points the golfer receives
gt; SP = 0
gt; If Handicap = 0 Then
gt; SP = Score
gt; ElseIf Handicap lt;= 18 Then
gt; For Count = 1 To Handicap
gt; If SI = Count Then
gt; SP = Score - 1
gt; Exit For
gt; Else
gt; SP = Score
gt; End If
gt; Next
gt; ElseIf Handicap gt;= 19 And Handicap lt;= 36 Then
gt; For Count = 1 To Handicap - 18
gt; If SI = Count Then
gt; SP = Score - 2
gt; Exit For
gt; Else
gt; SP = Score - 1
gt; End If
gt; Next
gt; Else 'Handicap 37 to 45
gt; For Count = 1 To Handicap - 36
gt; If SI = Count Then
gt; SP = Score - 3
gt; Exit For
gt; Else
gt; SP = Score - 2
gt; End If
gt; Next
gt; End If 'Checking Golfers Handicap
gt;
gt; 'Calculate the Stableford Points
gt; If SP - 1 = Par Then
gt; StablefordPoints = 1
gt; ElseIf SP = Par Then
gt; StablefordPoints = 2
gt; ElseIf SP 1 = Par Then
gt; StablefordPoints = 3
gt; ElseIf SP 2 = Par Then
gt; StablefordPoints = 4
gt; ElseIf SP 3 = Par Then
gt; StablefordPoints = 5
gt; ElseIf SP 4 = Par Then
gt; StablefordPoints = 6
gt; ElseIf SP 5 = Par Then
gt; StablefordPoints = 7
gt; ElseIf SP 6 = Par Then
gt; StablefordPoints = 8
gt; End If 'Calculating Stableford Points
gt; End If 'Score lt;gt; sEmpty
gt; End If
gt; End Function
gt; ======================================
gt;
gt; --ron
gt;

On Mon, 30 Jan 2006 07:06:30 -0800, quot;Davaquot; gt;
wrote:

gt;Thanks Ron, that wroks great aswell.
gt;

Glad to help.
--ron