nested @if's of more than 7

[GF]

I am attempting to adjust gross golf scores by hole
according to handicap.I must consider handicaps of less
than 0, 0-18, 19-36 and holes handicapped 1-18.
The way I have written them produces approximately 9
@if's and I get a "false" answer.

 

[Arvi Laanemets]

And what will be the algorithm? How are handicaps and holes related - I
haven't a clue about golf rules, so it'll be somewhat difficult to help
without additional info.

 

[Biff]

Hi Gf!

I have a workbook that I put together for another poster a
while back. If you'd like a copy I can email it to you.
It's just a rough draft to demonstrate the use of the
formulas.

This poster wanted something that calculates hdcp's and a
Stableford score. The Stableford system used was his own
modified version.

If interested, post a good email address (but munge it in
a way that I'll figure it out). My address is bogus.

Biff

 

[Guest]

Each hole is handicapped 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 a players handicap
is +2, he adds a stroke to each of the first 2
handicapped holes. If his handicap is -24, he deducts a
stroke on each hole, 1-18 and an additional stroke on
holes handicapped 1 thru 6. (His handicap minus 18).
Players with handicaps in excess of -36 deduct 2 strokes
per hole and an additional stoke on holes his handicap
exceeds 36.
If you can figure this out, you're a better man than me.

 

[Arvi Laanemets]

I'm not sure I did fully understand this, but maybe something like this:

Worksheet: Results
C1:T1 names of holes 1-18
C2:T2 number of stroces for hole
A3:A? players names
B3:B? players handicaps
A2:B2 enter headers for columns A:B (p.e. 'Players', 'Handicaps')
C3:T? actual strokes by player and hole
U3:U? players scores (the formula you need)
U2 header for column U, p.e. 'Score'

Create an additional sheet p.e. Calc
Calc!A2=Results!A2 and copy the link to ranges A2:B? and B1:T2
Into cell C3 enter the formula
=C$2-Results!C3+SIGN($B3)*(INT(ABS($B3)/18)+(COLUMN(C:C)-COLUMN($B:$B)<=MOD(
ABS($B3),18)))
or
=C$2-Results!C3-SIGN($B3)*(INT(ABS($B3)/18)+(COLUMN(C:C)-COLUMN($B:$B)<=MOD(
ABS($B3),18)))
(I'm not sure which of them is the right one)
and copy it to range C3:T?

Into cell U3 on sheet Results enter the formula
=SUM(Calc!C3:T3)
and copy it to range U3:U?

 

[Guest]

I can't see your original post, but from your second post I'm assuming you need the players new par based on his handicap.

Okay in this table E1 is the handicap, Stroke is the difficulty, column D is the new Par
=VLOOKUP($C2,Sheet4!A$2:$AP$20,MATCH($E$1,Sheet4!$A$2:$AP$2,0),FALSE)+B2
The range in Sheet4 A2:AP20.....Row2 are the handicaps(-4 thru 36) & Column A are the stroke (difficulty 1-18)

in the cells I've populated them with a 1 if the handicap is equal to or less than the stroke. Took me about 2 minutes to set up.

A B C D E
Hole Par Stroke New Par 23
1 4 18 5
2 4 17 5
3 4 16 5
4 4 15 5
5 5 14 6
6 3 13 4
7 4 12 5
8 3 11 4
9 4 10 5
10 4 9 5
11 4 8 5
12 5 7 6
13 4 6 5
14 4 5 6
15 4 4 6
16 3 3 5
17 4 2 6
18 5 1 7
72 95


Hope this helps, if you want me to send you a copy, post ur email address.


Harry

 

[Guest]

Just to add,

in the table in sheet 4.....if the the handicap is >18 then the a 2 populates the cell,
Also the table below, substitute Hole for stroke.

Harry

 

[Biff]

<snip>
If a players handicap
is +2, he adds a stroke to each of the first 2
handicapped holes.
<snip>

Well, not exactly! If a player has a "+" hdcp, they add
(give)strokes starting on the 18th hdcp hole. So a +2 hdcp
player gives strokes on the 18th and 17th hdcp holes.

Biff