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

=IF(HDCP18,IF(SI<=HDCP-18,2,1),IF(SI<=HDCP,1,0))

Hope this formula helps!
--
John C


"raynerman" wrote:

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

Hi

=IF(AND($C$118,$C$1-18=B2),2,IF($C$1=B2,1,0))

Where c1= handicap
B2 down is the stroke index
Put this in c2 abd adrag down

Mike

"raynerman" wrote:

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

=(C$1=$B2)+AND(C$118,C$1-18=$B2) in C2, and copy down and across as
appropriate

assuming that your players' handicaps are in row 1 starting at C1, and that
your stroke index for each hole is in B2:B19 (presumably hole number in
column A?).
--
David Biddulph

"raynerman" wrote in message
...
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

I like that. 3 different approaches & solutions to the same problem.
"raynerman" wrote in message
...
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

Or =(C$1=$B2)+(C$118)*(C$1-18=$B2) , of course.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=(C$1=$B2)+AND(C$118,C$1-18=$B2) in C2, and copy down and across as
appropriate

assuming that your players' handicaps are in row 1 starting at C1, and
that your stroke index for each hole is in B2:B19 (presumably hole number
in column A?).
--
David Biddulph

"raynerman" wrote in message
...
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

Another one...

With a max player hdcp of 28...

B1 = players hdcp
A2:A19 = hole hdcp

Entered in B2 and copied down to B19:

=LOOKUP(B$1-A2,{-18,0,18},{0,1,2})


--
Biff
Microsoft Excel MVP


"raynerman" wrote in message
...
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