Too many nested functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
Back
Top