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

"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

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

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

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

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

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