Too many nested functions
 

I am trying to create a bowling scoring worksheet, and have got it worked out
great, except that I was using nested IFs to get the scores. It works great
up to the 7th one, but I need about 10 to get the job done, so am looking for
an alternative solution. My formula using IFs is:

=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, "")))))))

I need to continue to test B4 & C4 about 3 more times to get all the
possible entries; and until the bowling scoring parameters are met, I want a
blank in the cell.

What should I use instead? I have tried looking into a =INDEX(range,
MATCH(ball1), MATCH(ball2) and looking that up in a table, but am having
difficulties with situations such as no entry {""}, strike{X}, and spare{/}.

Thank you in advance for any assistance, and feel free to ask for any
clarification I may have overlooked.

F6Hawk,

You could get rid of the nesting by using 0s as your returns for false
valus, something like:

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

HTH,
Bernie
MS Excel MVP

"F6Hawk" wrote in message
...
I am trying to create a bowling scoring worksheet, and have got it worked
out
great, except that I was using nested IFs to get the scores. It works
great
up to the 7th one, but I need about 10 to get the job done, so am looking
for
an alternative solution. My formula using IFs is:

=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, "")))))))

I need to continue to test B4 & C4 about 3 more times to get all the
possible entries; and until the bowling scoring parameters are met, I want
a
blank in the cell.

What should I use instead? I have tried looking into a =INDEX(range,
MATCH(ball1), MATCH(ball2) and looking that up in a table, but am having
difficulties with situations such as no entry {""}, strike{X}, and
spare{/}.

Thank you in advance for any assistance, and feel free to ask for any
clarification I may have overlooked.

This, of course, implies that the tests are mutually exclusive. If B4="X" ,
E4="X" and H4="X" and F4="/" then the result would be 50. Would be fine it
the same three cells were being test in each section of the sum.

Bernard


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
F6Hawk,

You could get rid of the nesting by using 0s as your returns for false
valus, something like:

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

HTH,
Bernie
MS Excel MVP

"F6Hawk" wrote in message
...
I am trying to create a bowling scoring worksheet, and have got it worked
out
great, except that I was using nested IFs to get the scores. It works
great
up to the 7th one, but I need about 10 to get the job done, so am looking
for
an alternative solution. My formula using IFs is:

=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, "")))))))

I need to continue to test B4 & C4 about 3 more times to get all the
possible entries; and until the bowling scoring parameters are met, I
want
a
blank in the cell.

What should I use instead? I have tried looking into a =INDEX(range,
MATCH(ball1), MATCH(ball2) and looking that up in a table, but am having
difficulties with situations such as no entry {""}, strike{X}, and
spare{/}.

Thank you in advance for any assistance, and feel free to ask for any
clarification I may have overlooked.

Bernard,

Yes, they are mutually exclusive. That's the nature of bowling scoring: if
E4 is an "X", then F4 cannot be "/". I'm not sure of his column structure,
but it appears that B and C will be the first "frame", E and F the second
frame, H and I, etc... for emtry, then D, G, J... for calculations.

Bernie


"Bernard Liengme" wrote in message
...
This, of course, implies that the tests are mutually exclusive. If B4="X"
, E4="X" and H4="X" and F4="/" then the result would be 50. Would be fine
it the same three cells were being test in each section of the sum.

Bernard


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
F6Hawk,

You could get rid of the nesting by using 0s as your returns for false
valus, something like:

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

HTH,
Bernie
MS Excel MVP

"F6Hawk" wrote in message
...
I am trying to create a bowling scoring worksheet, and have got it
worked
out
great, except that I was using nested IFs to get the scores. It works
great
up to the 7th one, but I need about 10 to get the job done, so am
looking
for
an alternative solution. My formula using IFs is:

=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, "")))))))

I need to continue to test B4 & C4 about 3 more times to get all the
possible entries; and until the bowling scoring parameters are met, I
want
a
blank in the cell.

What should I use instead? I have tried looking into a =INDEX(range,
MATCH(ball1), MATCH(ball2) and looking that up in a table, but am having
difficulties with situations such as no entry {""}, strike{X}, and
spare{/}.

Thank you in advance for any assistance, and feel free to ask for any
clarification I may have overlooked.

You are 100% correct, Bernie. Thanks for the tip, I didn't realize that your
method actually "broke" the nest on the IFs. That will probably do the trick!

Thanks fellas for the input!

Dave

"Bernie Deitrick" wrote:

Bernard,

Yes, they are mutually exclusive. That's the nature of bowling scoring: if
E4 is an "X", then F4 cannot be "/". I'm not sure of his column structure,
but it appears that B and C will be the first "frame", E and F the second
frame, H and I, etc... for emtry, then D, G, J... for calculations.

Bernie


"Bernard Liengme" wrote in message
...
This, of course, implies that the tests are mutually exclusive. If B4="X"
, E4="X" and H4="X" and F4="/" then the result would be 50. Would be fine
it the same three cells were being test in each section of the sum.

Bernard