If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nesting limit
i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? =IF(AND(Y725,Y1720,Y1730),V33,IF(AND(Y725,Y17 30,Y1740),V34,IF(AND(Y725,Y1740),V35,IF(AND(Y7 24,Y736,Y1730),V37,IF(AND(Y724,Y736,Y1730,Y17 40),V38,IF(AND(Y724,Y736,Y1740),V39,IF(AND(Y7 36,Y1730),V41,IF(AND(Y736,Y1730,Y1740),V42,IF( AND(Y736,Y1740,Y1740),V43)))))))
|
#2
|
|||
|
|||
Nesting limit
Hi Steve
is there any alogorithmn / logic behind this values / comparissons? -- Regards Frank Kabel Frankfurt, Germany Steve Bursach wrote: i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? =IF(AND(Y725,Y1720,Y1730),V33,IF(AND(Y725,Y17 30,Y1740),V34,IF(AND (Y725,Y1740),V35,IF(AND(Y724,Y736,Y1730),V37, IF(AND(Y724,Y736,Y1 730,Y1740),V38,IF(AND(Y724,Y736,Y1740),V39,IF (AND(Y736,Y1730),V4 1,IF(AND(Y736,Y1730,Y1740),V42,IF(AND(Y736,Y17 40,Y1740),V43)))))) ) |
#3
|
|||
|
|||
Nesting limit
Are you sure you're conditions are correct? What if Y7 25 and Y17 =30?
The last one is AND(Y736,Y1740,Y1740) which will always return FALSE. In article , "Steve Bursach" wrote: i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? =IF(AND(Y725,Y1720,Y1730),V33,IF(AND(Y725,Y17 30,Y1740),V34,IF(AND(Y725, Y1740),V35,IF(AND(Y724,Y736,Y1730),V37,IF(AND( Y724,Y736,Y1730,Y1740),V 38,IF(AND(Y724,Y736,Y1740),V39,IF(AND(Y736,Y17 30),V41,IF(AND(Y736,Y1730 ,Y1740),V42,IF(AND(Y736,Y1740,Y1740),V43)))))) ) |
#4
|
|||
|
|||
Nesting limit
On Thu, 13 May 2004 10:01:04 -0700, "Steve Bursach"
wrote: i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? =IF(AND(Y725,Y1720,Y1730),V33,IF(AND(Y725,Y17 30,Y1740),V34,IF(AND(Y725,Y1740),V35,IF(AND(Y7 24,Y736,Y1730),V37,IF(AND(Y724,Y736,Y1730,Y17 40),V38,IF(AND(Y724,Y736,Y1740),V39,IF(AND(Y7 36,Y1730),V41,IF(AND(Y736,Y1730,Y1740),V42,IF( AND(Y736,Y1740,Y1740),V43))))))) First of all, your formula has a number of undefined issues and redundancies as written. For example, what do you want if Y7=20 and Y17=15? There are also a bunch of what I assume are typo's in the formula you posted. I would use an entirely different approach and compute the offset using the data you have put forth. Not knowing what you want to do with the undefined numbers makes it tough, though. Here is one approach: =OFFSET(V28,MATCH(Y7,{0,25,36})*4+MATCH(Y17,{20,30 ,40}),) However, it will always give an error if Y1720. In your example, Y1720 was only undefined if Y725. Also, you may have to change the boundaries (the array constants) depending on exactly what you want to happen on the boundaries. But this should give you some ideas for a different approach that will not run into the nesting issues. --ron |
#5
|
|||
|
|||
Nesting limit
no it is part of a bill of materials spread sheet and it looks up a certian part.
|
#6
|
|||
|
|||
Nesting limit
this is part of a bill of materials spread sheet it looks up a spacific part accorrding how it is written. it works fine if i anly have 7 but i need 9
|
#7
|
|||
|
|||
Nesting limit
On Thu, 13 May 2004 13:41:06 -0700, "Steve"
wrote: this is part of a bill of materials spread sheet it looks up a spacific part accorrding how it is written. it works fine if i anly have 7 but i need 9 As I said, you need to adopt a different algorhithm -- I suggested one -- and you also need to clean up your typos and logical inconsistencies. --ron |
#8
|
|||
|
|||
Nesting limit
"=?Utf-8?B?U3RldmU=?=" wrote...
this is part of a bill of materials spread sheet it looks up a spacific part accorrding how it is written. it works fine if i anly have 7 but i need 9 You are *FAILING* to understand a point that two of your respondents so far have made. Your formula WILL *FAIL* if the Y7 and Y17 cells exactly equal certain values because your formula uses only and comparisons, with no = or = comparisons. Even if you needed only 7 conditions/nesting levels, your formula would *FAIL* under these circumstances. -- To top-post is human, to bottom-post and snip is sublime. |
#9
|
|||
|
|||
Nesting limit
"=?Utf-8?B?U3RldmUgQnVyc2FjaA==?=" wrote...
i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? [reformatted] =IF(AND(Y725,Y1720,Y1730),V33, IF(AND(Y725,Y1730,Y1740),V34, IF(AND(Y725,Y1740),V35, IF(AND(Y724,Y736,Y1730),V37, IF(AND(Y724,Y736,Y1730,Y1740),V38, IF(AND(Y724,Y736,Y1740),V39, IF(AND(Y736,Y1730),V41, IF(AND(Y736,Y1730,Y1740),V42, IF(AND(Y736,Y1740,Y1740),V43))))))) It appears you're assuming Y7 and Y17 will always be whole numbers. Is that correct? If so, it'd allow for considerable simplification. Reformatting the conditions, ______Y7 25 20 Y17 30 (1a) _________________30 Y17 40 _________________40 Y17 24 Y7 36 Y17 30 (1b) _________________30 Y17 40 Y7 24 Y7 36 Y17 40 (2) 36 Y7 Y17 30 _________________30 Y17 40 _________________40 Y17 40 (3) Questions/Comments ------------------ (1a) and (1b) - Should the ranges applied to Y17 when Y7 25 or Y7 24 be different, that is, do you really want to check whether Y17 is greater than 20 when Y7 is less than 25 but not check that Y17 is greater than 20 when Y7 is greater than 24? (2) - This is almost certainly a typo. I can't believe you didn't mean AND(Y724,Y736,Y1740) (3) - This is almost certainly a typo since Y1740 and Y1740 is impossible, so the final IF condition is necessarily always False. You'd do well to pay attention to the respondents who had already pointed these problems out rather than just parrot the line that your formula 'works' (a demonstrably false assertion) with 7 nesting levels but fails with 9 (because Excel doesn's support so many). If Y7 and Y17 may only contain whole numbers, then you could use lookup tables. I'm going to assume (2) and (3) are bugs in your formula, but I'll leave the difference between (1a) and (1b) as-is. That changes the conditions to Y7 25 20 Y17 30 _________________30 = Y17 40 _________________40 = Y17 25 = Y7 36 Y17 30 _________________30 = Y17 40 _________________40 = Y17 36 = Y7 Y17 30 _________________30 = Y17 40 _________________40 = Y17 And at that point you could use something like =IF(AND(Y725,Y17=20),FALSE,INDEX((V33:V35,V37:V3 9,V41:V43), MATCH(Y17,{-1E300;30;40})),1,MATCH(Y7,{-1E300;25;36}))) As long as your other conditions involved the same Y17 bands, you could add other ranges to the multiple area range fist argument to INDEX and add values to the array second argument to the second MATCH. Heck, if there were no need for the Y1720 condition when Y725, this could be simplified to =LOOKUP(Y17,{-1E300;30;40},OFFSET(V33:V35,4*(MATCH(Y7,{-1E300;25;36})-1),0,3,1)) Any time you have a formula with many nested IFs referring to the same cells you can almost certainly replace it with some LOOKUP formula. -- To top-post is human, to bottom-post and snip is sublime. |
Thread Tools | |
Display Modes | |
|
|