Data calculations require more than 7 nested functions

F

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
 
D

Dave Peterson

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

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

K.S.Warrier

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top