if a1= 5 & b1 is 20 how do I make b1 17 but if b1<18 leave as is
 

I'm setting up a spreadsheet to determine winners of a 3 person golf
scramble. The highest handicap of a team member can not be greater than 12
strokes higher than the low handicap team members handicap. For example if
the low handicap member of the team has a 5 handicap, the other 2 members of
the team can not have handicaps over 17 so if one or both are above 17 then
their handicaps must be reduced to 17. In the subject above I would put each
person's handicap in a different cell e.g. A1, B1, and C1 with the total of
the 3 in D1. Then I would use another cell, say E1, to calculate 15% of the
total in D1. I'm using Office 2003.

if a1= 5 & b1 is 20 how do I make b1 17 but if b1<18 leave as is
 

In A1 enter low handicap.

B1 and C1 enter the other two handicaps.

In A2 enter =A1

B2 enter =IF(B1-$A$112,$A$1+12,B1)

Copy to C2

In D2 enter =SUM(A2:C2)

In E2 enter =D2*.15

I think you would want 85% of the total but that's up to you.


Gord Dibben MS Excel MVP

On Fri, 6 Mar 2009 15:59:01 -0800, Golf Scrambler <Golf
wrote:

I'm setting up a spreadsheet to determine winners of a 3 person golf
scramble. The highest handicap of a team member can not be greater than 12
strokes higher than the low handicap team members handicap. For example if
the low handicap member of the team has a 5 handicap, the other 2 members of
the team can not have handicaps over 17 so if one or both are above 17 then
their handicaps must be reduced to 17. In the subject above I would put each
person's handicap in a different cell e.g. A1, B1, and C1 with the total of
the 3 in D1. Then I would use another cell, say E1, to calculate 15% of the
total in D1. I'm using Office 2003.

if a1= 5 & b1 is 20 how do I make b1 17 but if b1<18 leave as
 

Thank you Gord.


"Gord Dibben" wrote:

In A1 enter low handicap.

B1 and C1 enter the other two handicaps.

In A2 enter =A1

B2 enter =IF(B1-$A$112,$A$1+12,B1)

Copy to C2

In D2 enter =SUM(A2:C2)

In E2 enter =D2*.15

I think you would want 85% of the total but that's up to you.


Gord Dibben MS Excel MVP

On Fri, 6 Mar 2009 15:59:01 -0800, Golf Scrambler <Golf
wrote:

I'm setting up a spreadsheet to determine winners of a 3 person golf
scramble. The highest handicap of a team member can not be greater than 12
strokes higher than the low handicap team members handicap. For example if
the low handicap member of the team has a 5 handicap, the other 2 members of
the team can not have handicaps over 17 so if one or both are above 17 then
their handicaps must be reduced to 17. In the subject above I would put each
person's handicap in a different cell e.g. A1, B1, and C1 with the total of
the 3 in D1. Then I would use another cell, say E1, to calculate 15% of the
total in D1. I'm using Office 2003.