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)))))))

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))))))
)

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)))))) )

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

no it is part of a bill of materials spread sheet and it looks up a certian part.

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

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

"=?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.

"=?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.