Conditional Formula for Golf

[raynerman]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a scoring spreadsheet for tracking a golf competition. In it, players have a handicap (1-28) and each hole has a stroke index (1-18) which signifies how easy each hole is (1 hard, 18 easy).

I am trying to work out a formula that assigns the right number of shots each player based on the handicap and stroke index.

For example, if I have a handicap of 21, I will get 1 shot on all 18 holes, and extra shot on the three hardest holes (those with SI of 1 to 3).

So the formula is to return a value of 0, 1 or 2.

Can anyone help? Thanks in advance!

Nigel

 

[raynerman]

Took some solving but in case someone else stumbles on this with a similar question, the shots are calculated by:

(where G4 is starting handicap and D9 down the SI column)

=IF(G$4>=($D9+18),2,IF(G$4>=$D9,1,0))

Which makes the stableford points the interesting part. It doesn't work if a 19 handicap gets a hole in one on a par 5, but I think I can live with that...

(Where G9 the gross score, N9 the shots gained above and C9 the SI...)

=IF((G9-N9)=($C9-3),5,IF((G9-N9)=($C9-2),4,IF((G9-N9)=($C9-1),3,IF((G9-N9)=$C9,2,IF((G9-N9)=($C9+1),1,0)))))