Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Hi, Sorry for posting this twice but I got no replies from the first post.
I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
"kevhatch" wrote: Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=2 1 under par=3 2 under par=4 3 under par=5 4+under par=6 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd EDIT: If the Gross Score would return a value of "0" for the Net Score, then the points would be 1 more than is currently being returned, "-1" would be 2 more etc.etc. Thx in advance Kev |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
I looked at your post the last time (a couple of weeks ago, wasn't it?) but
your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Thx for your time Biff,
New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Hi!
Gross score for a hole returns a zero How can you have a gross score of 0? Or did you mean when the gross score cell is empty? Change the formula for the net score to: (I'm using the 17th hole cell that you mention in the post): =IF(Y11="","",Y11-INDEX(Chk!$A:$S,MATCH($C11,Chk!$A:$A,0),MATCH(Y$5, Chk!$A$5:$S$5,0))) Change the formula for the points to: =IF(Z11="","",IF(OR(Y$4-Z11<-1,Y$4-Z114),0,VLOOKUP(Y$4-Z11,{-1,1;0,2;1,3;2,4;3,5;4,6},2))) I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114) You'll notice that I changed it from 3 because if you aced the par 3 your total net strokes under par would be 4 and because you previously had Y$4-Z113, that was causing the return of 0. Also, in the Vlookup call you didn't have the 4 under values: 4,6. See if that works out for you. Biff "kevhatch" wrote in message ... Thx for your time Biff, New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Hi Biff and Kevhatch
Just a little side question here. Are you doing this scoring system for the USA or somewhere else. If it's Australia I'll get involved, otherwise I'll sit on the side. Regards Michael "Biff" wrote: Hi! Gross score for a hole returns a zero How can you have a gross score of 0? Or did you mean when the gross score cell is empty? Change the formula for the net score to: (I'm using the 17th hole cell that you mention in the post): =IF(Y11="","",Y11-INDEX(Chk!$A:$S,MATCH($C11,Chk!$A:$A,0),MATCH(Y$5, Chk!$A$5:$S$5,0))) Change the formula for the points to: =IF(Z11="","",IF(OR(Y$4-Z11<-1,Y$4-Z114),0,VLOOKUP(Y$4-Z11,{-1,1;0,2;1,3;2,4;3,5;4,6},2))) I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114) You'll notice that I changed it from 3 because if you aced the par 3 your total net strokes under par would be 4 and because you previously had Y$4-Z113, that was causing the return of 0. Also, in the Vlookup call you didn't have the 4 under values: 4,6. See if that works out for you. Biff "kevhatch" wrote in message ... Thx for your time Biff, New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
P.S. to Kev:
You really don't need that stroke chart, sheet CHK, but I didn't want to completely redo your setup so I just tweaked the formula you asked about. Michael, what do you have in mind? Stableford scoring is pretty much unique to each club. They make up their own rules. I did a score sheet a couple of weeks ago for a club where they only give 1/2 or 3/4 strokes for hdcp. Biff "Michael M" wrote in message ... Hi Biff and Kevhatch Just a little side question here. Are you doing this scoring system for the USA or somewhere else. If it's Australia I'll get involved, otherwise I'll sit on the side. Regards Michael "Biff" wrote: Hi! Gross score for a hole returns a zero How can you have a gross score of 0? Or did you mean when the gross score cell is empty? Change the formula for the net score to: (I'm using the 17th hole cell that you mention in the post): =IF(Y11="","",Y11-INDEX(Chk!$A:$S,MATCH($C11,Chk!$A:$A,0),MATCH(Y$5, Chk!$A$5:$S$5,0))) Change the formula for the points to: =IF(Z11="","",IF(OR(Y$4-Z11<-1,Y$4-Z114),0,VLOOKUP(Y$4-Z11,{-1,1;0,2;1,3;2,4;3,5;4,6},2))) I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114) You'll notice that I changed it from 3 because if you aced the par 3 your total net strokes under par would be 4 and because you previously had Y$4-Z113, that was causing the return of 0. Also, in the Vlookup call you didn't have the 4 under values: 4,6. See if that works out for you. Biff "kevhatch" wrote in message ... Thx for your time Biff, New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Thx Biff, will try that when I get home tonight and let you know.
"How can you have a gross score of 0? Or did you mean when the gross score cell is empty?" Highly unlikely I know, but if a 28 H/Capper aced a par 3 or Eagled a par four with a stroke index of 10 or below it would return a score of "-1" and "0" respectively, which when totalling Net strokes +28 would equal the Gross score. As it is at the moment, it is leaving the cell blank and not returning any Stableford points. Hope I understood correctly and that that is what you were asking about! "Biff" wrote: P.S. to Kev: You really don't need that stroke chart, sheet CHK, but I didn't want to completely redo your setup so I just tweaked the formula you asked about. Michael, what do you have in mind? Stableford scoring is pretty much unique to each club. They make up their own rules. I did a score sheet a couple of weeks ago for a club where they only give 1/2 or 3/4 strokes for hdcp. Biff "Michael M" wrote in message ... Hi Biff and Kevhatch Just a little side question here. Are you doing this scoring system for the USA or somewhere else. If it's Australia I'll get involved, otherwise I'll sit on the side. Regards Michael "Biff" wrote: Hi! Gross score for a hole returns a zero How can you have a gross score of 0? Or did you mean when the gross score cell is empty? Change the formula for the net score to: (I'm using the 17th hole cell that you mention in the post): =IF(Y11="","",Y11-INDEX(Chk!$A:$S,MATCH($C11,Chk!$A:$A,0),MATCH(Y$5, Chk!$A$5:$S$5,0))) Change the formula for the points to: =IF(Z11="","",IF(OR(Y$4-Z11<-1,Y$4-Z114),0,VLOOKUP(Y$4-Z11,{-1,1;0,2;1,3;2,4;3,5;4,6},2))) I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114) You'll notice that I changed it from 3 because if you aced the par 3 your total net strokes under par would be 4 and because you previously had Y$4-Z113, that was causing the return of 0. Also, in the Vlookup call you didn't have the 4 under values: 4,6. See if that works out for you. Biff "kevhatch" wrote in message ... Thx for your time Biff, New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Hi!
I think we're confusing the terminology. The gross score is the actual number of strokes. If you ace a par 3 the gross score is 1. The net score is the gross score adjusted for your hdcp and then the Stableford points is totally separate based on the net score. Biff "kevhatch" wrote in message ... Thx Biff, will try that when I get home tonight and let you know. "How can you have a gross score of 0? Or did you mean when the gross score cell is empty?" Highly unlikely I know, but if a 28 H/Capper aced a par 3 or Eagled a par four with a stroke index of 10 or below it would return a score of "-1" and "0" respectively, which when totalling Net strokes +28 would equal the Gross score. As it is at the moment, it is leaving the cell blank and not returning any Stableford points. Hope I understood correctly and that that is what you were asking about! "Biff" wrote: P.S. to Kev: You really don't need that stroke chart, sheet CHK, but I didn't want to completely redo your setup so I just tweaked the formula you asked about. Michael, what do you have in mind? Stableford scoring is pretty much unique to each club. They make up their own rules. I did a score sheet a couple of weeks ago for a club where they only give 1/2 or 3/4 strokes for hdcp. Biff "Michael M" wrote in message ... Hi Biff and Kevhatch Just a little side question here. Are you doing this scoring system for the USA or somewhere else. If it's Australia I'll get involved, otherwise I'll sit on the side. Regards Michael "Biff" wrote: Hi! Gross score for a hole returns a zero How can you have a gross score of 0? Or did you mean when the gross score cell is empty? Change the formula for the net score to: (I'm using the 17th hole cell that you mention in the post): =IF(Y11="","",Y11-INDEX(Chk!$A:$S,MATCH($C11,Chk!$A:$A,0),MATCH(Y$5, Chk!$A$5:$S$5,0))) Change the formula for the points to: =IF(Z11="","",IF(OR(Y$4-Z11<-1,Y$4-Z114),0,VLOOKUP(Y$4-Z11,{-1,1;0,2;1,3;2,4;3,5;4,6},2))) I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114) You'll notice that I changed it from 3 because if you aced the par 3 your total net strokes under par would be 4 and because you previously had Y$4-Z113, that was causing the return of 0. Also, in the Vlookup call you didn't have the 4 under values: 4,6. See if that works out for you. Biff "kevhatch" wrote in message ... Thx for your time Biff, New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Golf Scores
Hi Biff,
Yes, I understand the Gross/Net score, think probably just the way I wrote it. I meant......... """Highly unlikely I know, but if a 28 H/Capper aced a par 3 or Eagled a par four with a stroke index of 10 or below it should return a Net score of "-1"(1 stroke minus H/cap allowance of 2 strokes = -1) and "0" (2 strokes minus H/cap allowance of 2 strokes = 0) respectively, which when totalling Net strokes +28 would equal the Gross score.""" You also wrote:- "I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114)" I caniballised a larger programme I have that I use for competitions with friends. I think this is to take into account the Stroke Index and Par changes on the different courses we play. This is also why the "chk sheet is there". I've posted the whole programme, http://www.flypicture.com?display=updone&id=r9nwlKzf Sheets S.C. Day1,2 etc are the basis for my original post. Thx for you patience, wish I understood the Formulae better, learning bit by bit! Kev "Biff" wrote: Hi! I think we're confusing the terminology. The gross score is the actual number of strokes. If you ace a par 3 the gross score is 1. The net score is the gross score adjusted for your hdcp and then the Stableford points is totally separate based on the net score. Biff "kevhatch" wrote in message ... Thx Biff, will try that when I get home tonight and let you know. "How can you have a gross score of 0? Or did you mean when the gross score cell is empty?" Highly unlikely I know, but if a 28 H/Capper aced a par 3 or Eagled a par four with a stroke index of 10 or below it would return a score of "-1" and "0" respectively, which when totalling Net strokes +28 would equal the Gross score. As it is at the moment, it is leaving the cell blank and not returning any Stableford points. Hope I understood correctly and that that is what you were asking about! "Biff" wrote: P.S. to Kev: You really don't need that stroke chart, sheet CHK, but I didn't want to completely redo your setup so I just tweaked the formula you asked about. Michael, what do you have in mind? Stableford scoring is pretty much unique to each club. They make up their own rules. I did a score sheet a couple of weeks ago for a club where they only give 1/2 or 3/4 strokes for hdcp. Biff "Michael M" wrote in message ... Hi Biff and Kevhatch Just a little side question here. Are you doing this scoring system for the USA or somewhere else. If it's Australia I'll get involved, otherwise I'll sit on the side. Regards Michael "Biff" wrote: Hi! Gross score for a hole returns a zero How can you have a gross score of 0? Or did you mean when the gross score cell is empty? Change the formula for the net score to: (I'm using the 17th hole cell that you mention in the post): =IF(Y11="","",Y11-INDEX(Chk!$A:$S,MATCH($C11,Chk!$A:$A,0),MATCH(Y$5, Chk!$A$5:$S$5,0))) Change the formula for the points to: =IF(Z11="","",IF(OR(Y$4-Z11<-1,Y$4-Z114),0,VLOOKUP(Y$4-Z11,{-1,1;0,2;1,3;2,4;3,5;4,6},2))) I can't figure out what this expression is for: OR(Y$4-Z11<-1,Y$4-Z114) You'll notice that I changed it from 3 because if you aced the par 3 your total net strokes under par would be 4 and because you previously had Y$4-Z113, that was causing the return of 0. Also, in the Vlookup call you didn't have the 4 under values: 4,6. See if that works out for you. Biff "kevhatch" wrote in message ... Thx for your time Biff, New link below. Kev http://www.flypicture.com?display=updone&id=r9n3m6nY "Biff" wrote: I looked at your post the last time (a couple of weeks ago, wasn't it?) but your link had xml files. If you convert it to an xls file I'll have a look. Biff "kevhatch" wrote in message ... Hi, Sorry for posting this twice but I got no replies from the first post. I have a workbook for working out Net scores and Stableford points based on players handicap, par of the hole and stroke index of the hole (link below) I have had a lot of help from you guys with this(thanks) and would like to call on you once more! Hopefully it's just a formula tweak! The problem I am having is that when my Gross score for a hole returns a zero or minus Net score it won't show the points correctly i.e. 1 over par= 1point par=3 1 under par=2 2 under par=3 3 under par=4 4+under par=5 Hole 17 on the 19/04/2006 highlights the problem (I didn't get a hole in one :-(, just hit the wrong key when I entered the score, but I can dream!! Entering the correct score"2" has the same effect) Hope it makes sense to someone. http://www.flypicture.com?display=updone&id=rtD2lKXd Thx in advance Kev |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
golf handicap 4 of last 5 scores | Excel Discussion (Misc queries) | |||
conditional Formatting golf scores | Excel Discussion (Misc queries) | |||
sorting golf scores | Excel Discussion (Misc queries) | |||
excel for tracking golf scores or handicap | Excel Discussion (Misc queries) | |||
Golf Handicap Using Last 5 Scores | Excel Worksheet Functions |