Data calculations require more than 7 nested functions
 

I am trying to create a bowling score sheet, and up to 7 IFs works great:

=IF(AND(B4="X", E4="X", H4="X"), 30,
+IF(AND(B4="X", E4="X", H4="-"), 20,
+IF(AND(B4="X", E4="X", H4<""), 20+H4,
+IF(AND(B4="X", E4<"", F4="/"), 20,
+IF(AND(B4="X", E4="-", F4="-"), 10,
+IF(AND(B4="X", E4="-", F4<""), 10+F4,
+IF(AND(B4="X", E4<"", F4<""), 10+E4+F4, "")))))))

(balls 1&2 of frame 1 are cells B4 & C4, balls 1&2 of frame 2 are cells E4 &
F4, etc..)

but that is the limit, and I need about 3 or 4 more tests to ensure I get
all of the possibilities. I don't want anything to appear in the cell until
a score is required by bowling scoring convention.

I have tried looking into this using an INDEX(range, MATCH(ball1),
MATCH(ball2)) solution, but am having difficulties with things like no entry
{""}, strikes {X}, and spares {/}.

Another feature I need to incorporate is that if cell B4 is an "X", then I
don't want the user to be able to put anything in cell C4. Some sort of
validation test here that is based on the value of B4.

Thanks in advance for any assistance you can provide, and feel free to ask
for clarification if I left anything out.

Dave

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany


F6Hawk wrote:
I am trying to create a bowling score sheet, and up to 7 IFs works
great:

=IF(AND(B4="X", E4="X", H4="X"), 30,
+IF(AND(B4="X", E4="X", H4="-"), 20,
+IF(AND(B4="X", E4="X", H4<""), 20+H4,
+IF(AND(B4="X", E4<"", F4="/"), 20,
+IF(AND(B4="X", E4="-", F4="-"), 10,
+IF(AND(B4="X", E4="-", F4<""), 10+F4,
+IF(AND(B4="X", E4<"", F4<""), 10+E4+F4, "")))))))

(balls 1&2 of frame 1 are cells B4 & C4, balls 1&2 of frame 2 are
cells E4 & F4, etc..)

but that is the limit, and I need about 3 or 4 more tests to ensure I
get all of the possibilities. I don't want anything to appear in the
cell until a score is required by bowling scoring convention.

I have tried looking into this using an INDEX(range, MATCH(ball1),
MATCH(ball2)) solution, but am having difficulties with things like
no entry {""}, strikes {X}, and spares {/}.

Another feature I need to incorporate is that if cell B4 is an "X",
then I don't want the user to be able to put anything in cell C4.
Some sort of validation test here that is based on the value of B4.

Thanks in advance for any assistance you can provide, and feel free
to ask for clarification if I left anything out.

Dave

You may want to see how Harlan Grove approached it:
ftp://members.aol.com/hrlngrv/bowling.xls



F6Hawk wrote:

I am trying to create a bowling score sheet, and up to 7 IFs works great:

=IF(AND(B4="X", E4="X", H4="X"), 30,
+IF(AND(B4="X", E4="X", H4="-"), 20,
+IF(AND(B4="X", E4="X", H4<""), 20+H4,
+IF(AND(B4="X", E4<"", F4="/"), 20,
+IF(AND(B4="X", E4="-", F4="-"), 10,
+IF(AND(B4="X", E4="-", F4<""), 10+F4,
+IF(AND(B4="X", E4<"", F4<""), 10+E4+F4, "")))))))

(balls 1&2 of frame 1 are cells B4 & C4, balls 1&2 of frame 2 are cells E4 &
F4, etc..)

but that is the limit, and I need about 3 or 4 more tests to ensure I get
all of the possibilities. I don't want anything to appear in the cell until
a score is required by bowling scoring convention.

I have tried looking into this using an INDEX(range, MATCH(ball1),
MATCH(ball2)) solution, but am having difficulties with things like no entry
{""}, strikes {X}, and spares {/}.

Another feature I need to incorporate is that if cell B4 is an "X", then I
don't want the user to be able to put anything in cell C4. Some sort of
validation test here that is based on the value of B4.

Thanks in advance for any assistance you can provide, and feel free to ask
for clarification if I left anything out.

Dave

--

Dave Peterson

Hi,
I think that instead of treating the function as ' nested if ' one ,try this
as splitted closed ones using '+' in between each condition as shown
below.(If the condition is not satisfied ,""(blank) will be shown in each.By
this,we can use more than 7 functions in a fomula.

=if(and(b4="x",e4="x",h4="x'),30,"")+if(and(b4="x" ,e4="x",h4="-"),20,"")+.....(continue)
Thank you,
K.S.Warrier

"F6Hawk" wrote:

I am trying to create a bowling score sheet, and up to 7 IFs works great:

=IF(AND(B4="X", E4="X", H4="X"), 30,
+IF(AND(B4="X", E4="X", H4="-"), 20,
+IF(AND(B4="X", E4="X", H4<""), 20+H4,
+IF(AND(B4="X", E4<"", F4="/"), 20,
+IF(AND(B4="X", E4="-", F4="-"), 10,
+IF(AND(B4="X", E4="-", F4<""), 10+F4,
+IF(AND(B4="X", E4<"", F4<""), 10+E4+F4, "")))))))

(balls 1&2 of frame 1 are cells B4 & C4, balls 1&2 of frame 2 are cells E4 &
F4, etc..)

but that is the limit, and I need about 3 or 4 more tests to ensure I get
all of the possibilities. I don't want anything to appear in the cell until
a score is required by bowling scoring convention.

I have tried looking into this using an INDEX(range, MATCH(ball1),
MATCH(ball2)) solution, but am having difficulties with things like no entry
{""}, strikes {X}, and spares {/}.

Another feature I need to incorporate is that if cell B4 is an "X", then I
don't want the user to be able to put anything in cell C4. Some sort of
validation test here that is based on the value of B4.

Thanks in advance for any assistance you can provide, and feel free to ask
for clarification if I left anything out.

Dave

You may use 0 instead of ""(for blank cells) as addition with "" may show
ERROR as
#VALUE!
KSWarrier
"K.S.Warrier" wrote:

Hi,
I think that instead of treating the function as ' nested if ' one ,try this
as splitted closed ones using '+' in between each condition as shown
below.(If the condition is not satisfied ,""(blank) will be shown in each.By
this,we can use more than 7 functions in a fomula.

=if(and(b4="x",e4="x",h4="x'),30,"")+if(and(b4="x" ,e4="x",h4="-"),20,"")+.....(continue)
Thank you,
K.S.Warrier

"F6Hawk" wrote:

I am trying to create a bowling score sheet, and up to 7 IFs works great:

=IF(AND(B4="X", E4="X", H4="X"), 30,
+IF(AND(B4="X", E4="X", H4="-"), 20,
+IF(AND(B4="X", E4="X", H4<""), 20+H4,
+IF(AND(B4="X", E4<"", F4="/"), 20,
+IF(AND(B4="X", E4="-", F4="-"), 10,
+IF(AND(B4="X", E4="-", F4<""), 10+F4,
+IF(AND(B4="X", E4<"", F4<""), 10+E4+F4, "")))))))

(balls 1&2 of frame 1 are cells B4 & C4, balls 1&2 of frame 2 are cells E4 &
F4, etc..)

but that is the limit, and I need about 3 or 4 more tests to ensure I get
all of the possibilities. I don't want anything to appear in the cell until
a score is required by bowling scoring convention.

I have tried looking into this using an INDEX(range, MATCH(ball1),
MATCH(ball2)) solution, but am having difficulties with things like no entry
{""}, strikes {X}, and spares {/}.

Another feature I need to incorporate is that if cell B4 is an "X", then I
don't want the user to be able to put anything in cell C4. Some sort of
validation test here that is based on the value of B4.

Thanks in advance for any assistance you can provide, and feel free to ask
for clarification if I left anything out.

Dave