Golf scorecard again

Is there a formula for this?
Stableford Golf Scores.
To work out a players total points based on their handicap.


Each hole is handicapped (Stroke Index 1-18). The players handicap
indicates the hole upon which he can deduct a stroke,
ie. Players handicap is -4, he deducts a stroke on each of the first four
handicapped holes.
If his handicap is -28, he deducts a stroke on each hole, 1-18 and an
additional stroke on holes handicapped 1 thru 10. (His handicap minus 18).
Players handicaps run from -0 to -28.

Points are scored as follows:
1 over par= 1pt
par= 2pt
1 under par= 3pt
2 under par= 4pt
3 under par= 5pt

I have started one based on a 28 handicap which I think is correct, and
posted it here:

http://www.flypicture.com?display=updone&id=qtr1m6U=

Is there a way it can be worked with variable Stroke Indexes (for different
courses) and handicaps (different players)?
Sounds impossible to me but I'm sure you guys can figure it out.
Thx

That link isn't working, try this one:
http://www.flypicture.com?display=updone&id=qtr0k6g=

One play ..

Put in D6:
=IF(C6="",0,IF(C$4<$B6-18,C6-2,C6-1))

Put in E6:
=IF(D6=0,0,IF(OR(C$3-D6<-1,C$3-D6>3),0,VLOOKUP(C$3-D6,{-1,1;0,2;1,3;2,4;3,5}
,2)))

Select D6:E6 and copy / paste to G6:H6, J6:K6, M6:N6, P6:Q6, S6:T6, V6:W6,
Y6:Z6, AB6:AC6

Then just select D6:AD6 and fill down as many rows as required (but erase
all the dummy scores in row 6 first)

Sample file with the implemented construct available at:
http://www.savefile.com/files/8674458
File: GolfScoreCard_kevhatch_newusers_1.xls
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"kevhatch" <[email protected]> wrote in message
news:[email protected]...
> Is there a formula for this?
> Stableford Golf Scores.
> To work out a players total points based on their handicap.
>
> Each hole is handicapped (Stroke Index 1-18). The players handicap
> indicates the hole upon which he can deduct a stroke,
> ie. Players handicap is -4, he deducts a stroke on each of the first four
> handicapped holes.
> If his handicap is -28, he deducts a stroke on each hole, 1-18 and an
> additional stroke on holes handicapped 1 thru 10. (His handicap minus 18).
> Players handicaps run from -0 to -28.
>
> Points are scored as follows:
> 1 over par= 1pt
> par= 2pt
> 1 under par= 3pt
> 2 under par= 4pt
> 3 under par= 5pt
>
> I have started one based on a 28 handicap which I think is correct, and
> posted it here:
>
> http://www.flypicture.com?display=updone&id=qtr1m6U=
>
> Is there a way it can be worked with variable Stroke Indexes (for
different
> courses) and handicaps (different players)?
> Sounds impossible to me but I'm sure you guys can figure it out.
> Thx
>
---
> That link isn't working, try this one:
> http://www.flypicture.com?display=updone&id=qtr0k6g=
>

Thx Max,
That works great with a 28 handicap. Is there a way it can be made to work
if the h/cap were variable, as they can change for each player from time to
time?

Players handicap of:
1 to 18 allowed 1stroke only on stroke index holes corresponding to their
h/cap.
i.e. player h/cap of 3 allowed one stroke on holes of s/index 1 - 3 etc.

Players handicap of:
19 to 28 allowed 1 stroke on all 18 holes, in addition to 1 extra stroke on
each of the hardest holes corresponding to their h/cap.
i.e. player h/cap of 22 allowed one stroke for each hole plus one extra
stroke on holes of s/index 1 - 4 etc.
Thx again




"Max" wrote:

> One play ..
>
> Put in D6:
> =IF(C6="",0,IF(C$4<$B6-18,C6-2,C6-1))
>
> Put in E6:
> =IF(D6=0,0,IF(OR(C$3-D6<-1,C$3-D6>3),0,VLOOKUP(C$3-D6,{-1,1;0,2;1,3;2,4;3,5}
> ,2)))
>
> Select D6:E6 and copy / paste to G6:H6, J6:K6, M6:N6, P6:Q6, S6:T6, V6:W6,
> Y6:Z6, AB6:AC6
>
> Then just select D6:AD6 and fill down as many rows as required (but erase
> all the dummy scores in row 6 first)
>
> Sample file with the implemented construct available at:
> http://www.savefile.com/files/8674458
> File: GolfScoreCard_kevhatch_newusers_1.xls
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "kevhatch" <[email protected]> wrote in message
> news:[email protected]...
> > Is there a formula for this?
> > Stableford Golf Scores.
> > To work out a players total points based on their handicap.
> >
> > Each hole is handicapped (Stroke Index 1-18). The players handicap
> > indicates the hole upon which he can deduct a stroke,
> > ie. Players handicap is -4, he deducts a stroke on each of the first four
> > handicapped holes.
> > If his handicap is -28, he deducts a stroke on each hole, 1-18 and an
> > additional stroke on holes handicapped 1 thru 10. (His handicap minus 18).
> > Players handicaps run from -0 to -28.
> >
> > Points are scored as follows:
> > 1 over par= 1pt
> > par= 2pt
> > 1 under par= 3pt
> > 2 under par= 4pt
> > 3 under par= 5pt
> >
> > I have started one based on a 28 handicap which I think is correct, and
> > posted it here:
> >
> > http://www.flypicture.com?display=updone&id=qtr1m6U=
> >
> > Is there a way it can be worked with variable Stroke Indexes (for
> different
> > courses) and handicaps (different players)?
> > Sounds impossible to me but I'm sure you guys can figure it out.
> > Thx
> >
> ---
> > That link isn't working, try this one:
> > http://www.flypicture.com?display=updone&id=qtr0k6g=
> >
>
>
>

> .. made to work if the h/cap were variable ..

Thought variable handicaps (besides variable par and stroke indexes) was
already catered for in the suggested formulas, no? (Based, of course, on a
best effort interp of the complexities posed by a non-golfer <g>)

In the sample file provided, in Sheet1, try inputting other handicaps (but
with the same dummy scores for easy comparison) on subsequent rows 7 - 10
(I've pre-loaded the formulas to row10). Also keep the other 2 variables,
par and the stroke indexes unchanged to comparison. Check whether the
formula returns are as expected for a couple of test h/cap cases. Try it and
post back.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"kevhatch" <[email protected]> wrote in message
news:[email protected]...
> Thx Max,
> That works great with a 28 handicap. Is there a way it can be , as they
can change for each player from time to
> time?
>
> Players handicap of:
> 1 to 18 allowed 1stroke only on stroke index holes corresponding to their
> h/cap.
> i.e. player h/cap of 3 allowed one stroke on holes of s/index 1 - 3 etc.
>
> Players handicap of:
> 19 to 28 allowed 1 stroke on all 18 holes, in addition to 1 extra stroke
on
> each of the hardest holes corresponding to their h/cap.
> i.e. player h/cap of 22 allowed one stroke for each hole plus one extra
> stroke on holes of s/index 1 - 4 etc.
> Thx again

Typo in line:
> .. Also keep the other 2 variables, par and the stroke indexes
> unchanged to comparison...

2nd part should read:
> unchanged to aid comparison...

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

ok max, thx. sorry to be a pain.
I have applied and tested the formula and found a few discrepencies.
Uploaded here:

http://www.savefile.com/files/4982248

Table 1 shows:
what the extra shot allowance should be per hole according to players h/cap
and the stroke index of each hole.

Table 2 is showing the formula results. Discrepencies being the blue figures.

Hope all this makes sense.
Thx
Kev



"Max" wrote:

> Typo in line:
> > .. Also keep the other 2 variables, par and the stroke indexes
> > unchanged to comparison...
>
> 2nd part should read:
> > unchanged to aid comparison...
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Hi!

I tried downloading your file from the link you posted but it didn't work.
Bad link?

I was able to download Max's file.....

Is that how your file is laid out?

UGH! <vbg>

I have a sample file that does what you want. Handles hdcps from 0 to 54 and
includes the modified Stableford scoring system.

Here's a link:

http://s43.yousendit.com/d.aspx?id=2...M05IB3HJHRNUJS

That link expires in 7 days or 25 DL's, whichever occurs first.

Biff

"kevhatch" <[email protected]> wrote in message
news:[email protected]...
> ok max, thx. sorry to be a pain.
> I have applied and tested the formula and found a few discrepencies.
> Uploaded here:
>
> http://www.savefile.com/files/4982248
>
> Table 1 shows:
> what the extra shot allowance should be per hole according to players
> h/cap
> and the stroke index of each hole.
>
> Table 2 is showing the formula results. Discrepencies being the blue
> figures.
>
> Hope all this makes sense.
> Thx
> Kev
>
>
>
> "Max" wrote:
>
>> Typo in line:
>> > .. Also keep the other 2 variables, par and the stroke indexes
>> > unchanged to comparison...
>>
>> 2nd part should read:
>> > unchanged to aid comparison...
>>
>> --
>> Rgds
>> Max
>> xl 97
>> ---
>> GMT+8, 1° 22' N 103° 45' E
>> xdemechanik <at>yahoo<dot>com
>> ----
>>
>>
>>

Thanks for the clarification, Kev. Ok, thought it more straightforward to
just use your Table 1 (the one with the correct shot allowances) as a
reference source, and revise the earlier formula in D6 to read the figures
direct from Table 1 instead, via an INDEX(...,MATCH(...),MATCH(...)).

Here's the revised sample file: http://www.savefile.com/files/7533806
File: GolfScoreCard_kevhatch_newusers_2.xls

The revised construct hence reads as:

Put in D6:
=IF(C6="",0,C6-INDEX(Chk!$A:$S,MATCH($B6,Chk!$A:$A,0),MATCH(C$4,Chk!$A$5:$S$
5,0)))

(revised formula in D6, Table 1 is in sheet: Chk)

Put in E6:
=IF(D6=0,0,IF(OR(C$3-D6<-1,C$3-D6>3),0,VLOOKUP(C$3-D6,{-1,1;0,2;1,3;2,4;3,5}
,2)))

(no change to formula in E6)

Select D6:E6 and copy / paste to G6:H6, J6:K6, M6:N6, P6:Q6, S6:T6, V6:W6,
Y6:Z6, AB6:AC6

Then just select D6:AD6 and fill down as many rows as required (Erase all
the dummy scores in row 6 first)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"kevhatch" <[email protected]> wrote in message
news:[email protected]...
> ok max, thx. sorry to be a pain.
> I have applied and tested the formula and found a few discrepencies.
> Uploaded here:
>
> http://www.savefile.com/files/4982248
>
> Table 1 shows:
> what the extra shot allowance should be per hole according to players
h/cap
> and the stroke index of each hole.
>
> Table 2 is showing the formula results. Discrepencies being the blue
figures.
>
> Hope all this makes sense.
> Thx
> Kev

Thx Guys, excellent job. I'm learning..slowly!

"Max" wrote:

> Thanks for the clarification, Kev. Ok, thought it more straightforward to
> just use your Table 1 (the one with the correct shot allowances) as a
> reference source, and revise the earlier formula in D6 to read the figures
> direct from Table 1 instead, via an INDEX(...,MATCH(...),MATCH(...)).
>
> Here's the revised sample file: http://www.savefile.com/files/7533806
> File: GolfScoreCard_kevhatch_newusers_2.xls
>
> The revised construct hence reads as:
>
> Put in D6:
> =IF(C6="",0,C6-INDEX(Chk!$A:$S,MATCH($B6,Chk!$A:$A,0),MATCH(C$4,Chk!$A$5:$S$
> 5,0)))
>
> (revised formula in D6, Table 1 is in sheet: Chk)
>
> Put in E6:
> =IF(D6=0,0,IF(OR(C$3-D6<-1,C$3-D6>3),0,VLOOKUP(C$3-D6,{-1,1;0,2;1,3;2,4;3,5}
> ,2)))
>
> (no change to formula in E6)
>
> Select D6:E6 and copy / paste to G6:H6, J6:K6, M6:N6, P6:Q6, S6:T6, V6:W6,
> Y6:Z6, AB6:AC6
>
> Then just select D6:AD6 and fill down as many rows as required (Erase all
> the dummy scores in row 6 first)
>
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "kevhatch" <[email protected]> wrote in message
> news:[email protected]...
> > ok max, thx. sorry to be a pain.
> > I have applied and tested the formula and found a few discrepencies.
> > Uploaded here:
> >
> > http://www.savefile.com/files/4982248
> >
> > Table 1 shows:
> > what the extra shot allowance should be per hole according to players
> h/cap
> > and the stroke index of each hole.
> >
> > Table 2 is showing the formula results. Discrepencies being the blue
> figures.
> >
> > Hope all this makes sense.
> > Thx
> > Kev
>
>
>

Glad to hear it worked !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"kevhatch" <[email protected]> wrote in message
news:[email protected]...
> Thx Guys, excellent job. I'm learning..slowly!