Data calculations require more than 7 nested functions

[F6Hawk]

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

 

[Frank Kabel]

Hi
see your other post

 

[Dave Peterson]

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

 

[K.S.Warrier]

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

 

[K.S.Warrier]

You may use 0 instead of ""(for blank cells) as addition with "" may show
ERROR as
#VALUE!
KSWarrier