| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Statement for Golf Scores
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
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 -- Ian -- "Dava" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
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 "Dava" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
=if(a1="","",max(0,8-a1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=506306 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
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 "daddylonglegs" wrote: =if(a1="","",max(0,8-a1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=506306 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
Ignore my last thread, it works great after i have played with it a little
Thanks everyone Dave "Dava" wrote: 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 "daddylonglegs" wrote: =if(a1="","",max(0,8-a1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=506306 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
On Mon, 30 Jan 2006 03:50:27 -0800, "Dava"
wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
Thanks Ron, that wroks great aswell.
"Ron Rosenfeld" wrote: On Mon, 30 Jan 2006 03:50:27 -0800, "Dava" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Statement for Golf Scores
On Mon, 30 Jan 2006 07:06:30 -0800, "Dava"
wrote: Thanks Ron, that wroks great aswell. Glad to help. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| golf handicap 4 of last 5 scores | Excel Discussion (Misc queries) | |||
| SET statement tutorial | Excel Discussion (Misc queries) | |||
| Football Scores | Excel Discussion (Misc queries) | |||
| Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
| Golf Handicap Using Last 5 Scores | Excel Worksheet Functions | |||
Linear Mode
