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" <kevhatch@discussions.microsoft.com> wrote in message
news:DB69293F-3DAA-42E1-B1F4-715A347A02E1@microsoft.com...
> 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" <kevhatch@discussions.microsoft.com> wrote in message
> news:DB69293F-3DAA-42E1-B1F4-715A347A02E1@microsoft.com...
> > 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" <kevhatch@discussions.microsoft.com> wrote in message
news:A8BFFA9E-32A3-44D1-A137-011868F321A7@microsoft.com...
> 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
> ----
>
>
>