Formulas
 

I am trying to produce a spreadsheet for a golf society competition. I want
handicaps reduced each round based on the ranking of each competitor for that
round , eg

H'cap Points Position Total

Course 1 7 1

Course 2 5 2

Course 3 4

If a player comes first his handicap is reduced by 2, if second by 1, etc

How can I get the spreadsheet to look at the position cell, identify the
position, allocate the reduction based on that and then put it in the
handicap cell for course 2 by deducting that reduction from the handicap 1 ie
7 minus 2 for winning at course 1, 5 minus 1 for course 2 so that his new
handicap for course 3 will be four.

I have failed with both IF and LOOKUP! Any ideas most welcome

Geoff

Formulas
 

Set up as below
A B C
hc Pos
C1 7 1
C2 5 2
C3 4 1

In B3 (Where it say 5) type

=IF(C2=1,B2-2,IF(C2=2,B2-1,B2))

Drag down as necessary in column B.

Mike




"Geoff Allen" wrote:

I am trying to produce a spreadsheet for a golf society competition. I want
handicaps reduced each round based on the ranking of each competitor for that
round , eg

H'cap Points Position Total

Course 1 7 1

Course 2 5 2

Course 3 4

If a player comes first his handicap is reduced by 2, if second by 1, etc

How can I get the spreadsheet to look at the position cell, identify the
position, allocate the reduction based on that and then put it in the
handicap cell for course 2 by deducting that reduction from the handicap 1 ie
7 minus 2 for winning at course 1, 5 minus 1 for course 2 so that his new
handicap for course 3 will be four.

I have failed with both IF and LOOKUP! Any ideas most welcome

Geoff

Formulas
 

Mike,

Many thanks, works perfectly, simple when you know how!!

Thanks

Geoff

"Mike" wrote:

Set up as below
A B C
hc Pos
C1 7 1
C2 5 2
C3 4 1

In B3 (Where it say 5) type

=IF(C2=1,B2-2,IF(C2=2,B2-1,B2))

Drag down as necessary in column B.

Mike




"Geoff Allen" wrote:

I am trying to produce a spreadsheet for a golf society competition. I want
handicaps reduced each round based on the ranking of each competitor for that
round , eg

H'cap Points Position Total

Course 1 7 1

Course 2 5 2

Course 3 4

If a player comes first his handicap is reduced by 2, if second by 1, etc

How can I get the spreadsheet to look at the position cell, identify the
position, allocate the reduction based on that and then put it in the
handicap cell for course 2 by deducting that reduction from the handicap 1 ie
7 minus 2 for winning at course 1, 5 minus 1 for course 2 so that his new
handicap for course 3 will be four.

I have failed with both IF and LOOKUP! Any ideas most welcome

Geoff

Formulas
 

Shorter:

=IF(C2="","",B2-MAX(3-C2))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Mike" wrote in message
...
Set up as below
A B C
hc Pos
C1 7 1
C2 5 2
C3 4 1

In B3 (Where it say 5) type

=IF(C2=1,B2-2,IF(C2=2,B2-1,B2))

Drag down as necessary in column B.

Mike




"Geoff Allen" wrote:

I am trying to produce a spreadsheet for a golf society competition. I
want
handicaps reduced each round based on the ranking of each competitor for
that
round , eg

H'cap Points Position Total

Course 1 7 1

Course 2 5 2

Course 3 4

If a player comes first his handicap is reduced by 2, if second by 1, etc

How can I get the spreadsheet to look at the position cell, identify the
position, allocate the reduction based on that and then put it in the
handicap cell for course 2 by deducting that reduction from the handicap
1 ie
7 minus 2 for winning at course 1, 5 minus 1 for course 2 so that his new
handicap for course 3 will be four.

I have failed with both IF and LOOKUP! Any ideas most welcome

Geoff