nesting limitations
Posted by Philip Pavlich on October 30, 2000 5:57 PM
I want to add certain cells if certain conditions are met in another cell. The conditions are: if cell a1= 1, then sum cells J21:K21, if cell a1= 2, then sum cells J21:L21; ... if cell a1 = 12, then sum cells J21:V21; if cell a1=anything else, then return "HELP".
I tried nesting this but I think I went over the 7 level limit. At least I think thats the problem. Any ideas? Thanks.
Posted by Ivan Moala on October 30, 2000 11:47 PM
You haven't gone past the 7 limitation, it may
well be the format you have it in;
Try something like this ??
=IF(A1=1,SUM(J21:K21),IF(A1=12,SUM(J21:V21),"Help"))
Ivan
Posted by Philip on October 31, 2000 6:50 AM
Thanks Ivan. I tried that, but when I get all 12 arguments in there, I get an error message. However, I tried splitting it up and it worked. I can place the second part of the formula in another cell and hide it. Thanks again for your help. Philip
Posted by Tim Francis-Wright on October 31, 2000 9:18 AM
You might also try using the CHOOSE function:
=CHOOSE(a1,[answer1],...,[answern])
The only problem is that it expects A1 to
have a integer value from 1 to the number
of possible answers. So, you might use
=IF(or(a1<1,a1>12,a1<>INT(a1)),"HELP",SUM(CHOOSE(A1,J21:K21,J21:L21,J21:M21 [etc] , J21:V21)))
HTH
