A better way than nested IF?

[wasiii]

I am having a problem constructing a golf scoring sheet that tracks
team skins with team rotation every 6 holes and provides for
carryovers across the 6 hole boundaries. I have a data entry array(#1)
for entering the scores for each of 4 players, an array(#2) for
calculating the winning team and returning a 1, or a 0 if it is a
carryover for each of the two teams. I have a third array(#3) to
reconcile the carryovers based on testing the 1 or 0 for each hole (in
array #2) and if 0 looking ahead until a 1 is found and returning a 1
for that cell. I can do this by nesting IF functions which test for a
0 or 1 for each team for each hole, however I encounter the 7 nesting
limit in Excel 2000.
I am wondering if this is something that can be practically achieved
in excel, or is a more robust language required which can call a
subroutine to evaluate the carryover cells.

Thank in advance,

Bill

 

[T. Valko]

Kind of hard to visualize what you're trying to do from you description.
You're using a nested IF formula to look for 1 or 0.

How about describing your layout (in detail) and explain what you want to
happen when you find a 1 or 0.

 

[wasiii]

Kind of hard to visualize what you're trying to do from you description.
You're using a nested IF formula to look for 1 or 0.

How about describing your layout (in detail) and explain what you want to
happen when you find a 1 or 0.

Thanks for your reply.

Sorry, didn't mean to be vague.

Array #1 is pretty straight forward (enter scores)
Array #2 evaluates the scores based on 2 teams of 2 players each for
every 6 holes;

Drivers- 4x6 array for holes 1-6 values returned from evaluating
scoring array (#1)
Player1 - team 1 hole 1-6, 1 if team wins, 0 if team loses or ties,
E40:J40
Player2 - team 1 hole 1-6, 1 if team wins, 0 if team loses or ties,
E41:J41
Player3 - team 2 hole 1-6, 1 if team wins, 0 if team loses or ties,
E42:J42
Player4 - team 2 hole 1-6, 1 if team wins, 0 if team loses or ties,
E43:J43

Carts- 4x6 array for holes 7-12 values returned from evaluating
scoring array (#1)

Player 1 - team 1 hole 7-12 1 if team wins, 0 if team loses or ties,
K45:Q45
Player 3 - team 1 hole 7-12 1 if team wins, 0 if team loses or ties,
K46:Q46
Player 2 - team 2 hole 7-12 1 if team wins, 0 if team loses or ties,
K47:Q47
Player 4 - team 2 hole 7-12 1 if team wins, 0 if team loses or ties,
K48:Q48

Cross- 4x6 array for holes 13-18 values returned from evaluating
scoring array (#1)

Player 1 - team 1 hole 13-19 1 if team wins, 0 if team loses or ties,
R50:W50
Player 4 - team 1 hole 13-19 1 if team wins, 0 if team loses or ties,
R51:W51
Player 2 - team 2 hole 13-19 1 if team wins, 0 if team loses or ties,
R52:W52
Player 3 - team 2 hole 13-19 1 if team wins, 0 if team loses or ties,
R53:W53

Carryover Arrays #3 to reconcile cells in #2 arrays where all 0s have
been returned for that hole.

For Drivers

Player 1- team 1 hole 1-6 evaluate E40-J40, E42-J42 - K45-Q45, K47-
Q47, R50-W50, R53-W53
All 18 holes on the first team (Drivers) need to be evaluated in the
case of all ties until the 18th hole.

IF(E40>E42,1,((IF(E42>E40,0,(IF(F40>F42,1,(IF(F42>40,0(IF........IF
(J40>J42,1,(IF(J42>J40,0,(IF... next 6 holes K45..Q45..etc.

Player 2- team 1 hole 1-6 copy data from Player1 results

Player 3 - team 2 hole 1-6 same as above (Player1) with operators
inverted (<,>)

Player 4 - team 2 hole 1-6 copy data from Player 3 results

Remaining 2 teams use similar evaluation

The idea is if a carryover is won by a changed team, they claim
carryovers from the previous team configuration.

I know this is not a terribly elegant solution, but my Excel
experience has been with more generic problems.

Hope this helps,

Bill

 

[T. Valko]

Sorry, but I'm not able to follow your setup.

--
Biff
Microsoft Excel MVP

Kind of hard to visualize what you're trying to do from you description.
You're using a nested IF formula to look for 1 or 0.

How about describing your layout (in detail) and explain what you want to
happen when you find a 1 or 0.

Thanks for your reply.

Sorry, didn't mean to be vague.

Array #1 is pretty straight forward (enter scores)
Array #2 evaluates the scores based on 2 teams of 2 players each for
every 6 holes;

Drivers- 4x6 array for holes 1-6 values returned from evaluating
scoring array (#1)
Player1 - team 1 hole 1-6, 1 if team wins, 0 if team loses or ties,
E40:J40
Player2 - team 1 hole 1-6, 1 if team wins, 0 if team loses or ties,
E41:J41
Player3 - team 2 hole 1-6, 1 if team wins, 0 if team loses or ties,
E42:J42
Player4 - team 2 hole 1-6, 1 if team wins, 0 if team loses or ties,
E43:J43

Carts- 4x6 array for holes 7-12 values returned from evaluating
scoring array (#1)

Player 1 - team 1 hole 7-12 1 if team wins, 0 if team loses or ties,
K45:Q45
Player 3 - team 1 hole 7-12 1 if team wins, 0 if team loses or ties,
K46:Q46
Player 2 - team 2 hole 7-12 1 if team wins, 0 if team loses or ties,
K47:Q47
Player 4 - team 2 hole 7-12 1 if team wins, 0 if team loses or ties,
K48:Q48

Cross- 4x6 array for holes 13-18 values returned from evaluating
scoring array (#1)

Player 1 - team 1 hole 13-19 1 if team wins, 0 if team loses or ties,
R50:W50
Player 4 - team 1 hole 13-19 1 if team wins, 0 if team loses or ties,
R51:W51
Player 2 - team 2 hole 13-19 1 if team wins, 0 if team loses or ties,
R52:W52
Player 3 - team 2 hole 13-19 1 if team wins, 0 if team loses or ties,
R53:W53

Carryover Arrays #3 to reconcile cells in #2 arrays where all 0s have
been returned for that hole.

For Drivers

Player 1- team 1 hole 1-6 evaluate E40-J40, E42-J42 - K45-Q45, K47-
Q47, R50-W50, R53-W53
All 18 holes on the first team (Drivers) need to be evaluated in the
case of all ties until the 18th hole.

IF(E40>E42,1,((IF(E42>E40,0,(IF(F40>F42,1,(IF(F42>40,0(IF........IF
(J40>J42,1,(IF(J42>J40,0,(IF... next 6 holes K45..Q45..etc.

Player 2- team 1 hole 1-6 copy data from Player1 results

Player 3 - team 2 hole 1-6 same as above (Player1) with operators
inverted (<,>)

Player 4 - team 2 hole 1-6 copy data from Player 3 results

Remaining 2 teams use similar evaluation

The idea is if a carryover is won by a changed team, they claim
carryovers from the previous team configuration.

I know this is not a terribly elegant solution, but my Excel
experience has been with more generic problems.

Hope this helps,

Bill