Nesting limit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? =IF(AND(Y7<25,Y17>20,Y17<30),V33,IF(AND(Y7<25,Y17>30,Y17<40),V34,IF(AND(Y7<25,Y17>40),V35,IF(AND(Y7>24,Y7<36,Y17<30),V37,IF(AND(Y7>24,Y7<36,Y17>30,Y17<40),V38,IF(AND(Y7<24,Y7<36,Y17<40),V39,IF(AND(Y7>36,Y17<30),V41,IF(AND(Y7>36,Y17>30,Y17<40),V42,IF(AND(Y7>36,Y17>40,Y17<40),V43)))))))
 
Hi Steve
is there any alogorithmn / logic behind this values / comparissons?

--
Regards
Frank Kabel
Frankfurt, Germany


Steve said:
i need to know how to get around the nesting limit with this fomula.
Anyone have any ideas?
Click to expand...
=IF(AND(Y7<25,Y17>20,Y17<30),V33,IF(AND(Y7<25,Y17>30,Y17<40),V34,IF(AND
(Y7<25,Y17>40),V35,IF(AND(Y7>24,Y7<36,Y17<30),V37,IF(AND(Y7>24,Y7<36,Y1
7>30,Y17<40),V38,IF(AND(Y7<24,Y7<36,Y17<40),V39,IF(AND(Y7>36,Y17<30),V4
1,IF(AND(Y7>36,Y17>30,Y17<40),V42,IF(AND(Y7>36,Y17>40,Y17<40),V43))))))
)
 
Are you sure you're conditions are correct? What if Y7 < 25 and Y17 =30?
The last one is

AND(Y7>36,Y17>40,Y17<40)

which will always return FALSE.
 
i need to know how to get around the nesting limit with this fomula. Anyone have any ideas? =IF(AND(Y7<25,Y17>20,Y17<30),V33,IF(AND(Y7<25,Y17>30,Y17<40),V34,IF(AND(Y7<25,Y17>40),V35,IF(AND(Y7>24,Y7<36,Y17<30),V37,IF(AND(Y7>24,Y7<36,Y17>30,Y17<40),V38,IF(AND(Y7<24,Y7<36,Y17<40),V39,IF(AND(Y7>36,Y17<30),V41,IF(AND(Y7>36,Y17>30,Y17<40),V42,IF(AND(Y7>36,Y17>40,Y17<40),V43)))))))
Click to expand...

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 Y17<20. In your example, Y17<20 was
only undefined if Y7<25. 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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

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.
 
i need to know how to get around the nesting limit with this fomula. Anyone
have any ideas?
[reformatted]
=IF(AND(Y7<25,Y17>20,Y17<30),V33,
IF(AND(Y7<25,Y17>30,Y17<40),V34,
IF(AND(Y7<25,Y17>40),V35,
IF(AND(Y7>24,Y7<36,Y17<30),V37,
IF(AND(Y7>24,Y7<36,Y17>30,Y17<40),V38,
IF(AND(Y7<24,Y7<36,Y17<40),V39,
IF(AND(Y7>36,Y17<30),V41,
IF(AND(Y7>36,Y17>30,Y17<40),V42,
IF(AND(Y7>36,Y17>40,Y17<40),V43)))))))
Click to expand...

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(Y7>24,Y7<36,Y17>40)

(3) - This is almost certainly a typo since Y17>40 and Y17<40 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(Y7<25,Y17<=20),FALSE,INDEX((V33:V35,V37:V39,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 Y17>20 condition when Y7<25, 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.
 
Back
Top