Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable canned functions | Excel Discussion (Misc queries) | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
Changing the language of built-in functions? | New Users to Excel | |||
nested ifs | Setting up and Configuration of Excel | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |