Results 1 to 15 of 15
Thread: Limit to Nested Booleans? XL97

20010123, 17:41 #1
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Limit to Nested Booleans? XL97
Odd problem. The following formula blows up as soon as I put the necessary NOT statement in.
=IF(AND(B1=8,OR(Input!E41="W",Input!E41="L")),N5,I F(NOT(OR(Input!E41="W",Input!E41="L",Input!E41="N" )),N13,IF(B3<120,O13,IF(AND(Input!E41="L",OR(Input !E37="B",Input!E37="F")),P13,IF(AND(Input!E41="W", OR(Input!E38="M",Input!E38="F")),Q13,IF(AND(Input! E41="L",<font color=red>NOT</font color=red>(OR(Input!E38="M",Input!E38="F"))),,R13))))))
I moved it to blank worksheet with an empty "Input" sheet, and exactly the same thing happened. Is there a limit to nested booleans in XL97? I guess I can't singlecell this one.
(As to the formula, don't ask!)John ... I float in liquid gardens
UTC 7ąDS

20010123, 18:37 #2
 Join Date
 Jan 2001
 Location
 Roanoke area, Virginia, USA
 Posts
 3,729
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Limit to Nested Booleans? XL97
i thought it was 7 but unless i count wrong, you are under that.
Do you have the right number of ()? But excel isn't complaining about that, only the not(or

20010123, 18:45 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Limit to Nested Booleans? XL97
Excel isn't very specific in its complaint about this one. It doesn't appear to be paren matches, although I've confused Excel on unbalanced parens plenty of times.
Counting all the logic operators (IF, AND, OR & NOT) there are 15 before the NOT I could't insert (I think). Moving the entire IF() statement around the problem section to another cell and having the parent formula reference only that child cell cured the problem.
I hit some limit, I'm just not sure what.John ... I float in liquid gardens
UTC 7ąDS

20010123, 18:53 #4
 Join Date
 Jan 2001
 Location
 Roanoke area, Virginia, USA
 Posts
 3,729
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Limit to Nested Booleans? XL97
the 7 i'm thinking of is for the IFs alone. check out some of the excel sites and you'll likely find the answer.
start at http://www.jwalk.com

20010123, 19:02 #5
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Limit to Nested Booleans? XL97
You have a limit of 7 on nested functions, and the Not is on the 7th level. Though I'm surprised it doesn't blow up on the Or after the Not as well.
Jon
PS Would a suggestion like "Split it up into more than one cell" be helpful, or should I just shut up.

20010123, 19:44 #6
 Join Date
 Jan 2001
 Location
 Roanoke area, Virginia, USA
 Posts
 3,729
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Limit to Nested Booleans? XL97
glad to know i had the number right at least [img]/w3timages/icons/smile.gif[/img] it was blowing up for me on the OR too.

20010123, 20:46 #7
 Join Date
 Jan 2001
 Location
 Tullahoma, Tennessee, USA
 Posts
 17
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Limit to Nested Booleans? XL97
I suggest creating a function to compute your desired results.
A simple example of a function would be:
(In your spreadsheet
A B C
1 1 1 =findanswer(A1,B1)
2
In your visual basic module (insert module)
Public Function FINDANSWER(ITEM1, ITEM2)
If ITEM1 = 1 And ITEM2 = 2 Then
FINDANSWER = 12
ElseIf ITEM1 = 1 And ITEM2 = 1 Then
FINDANSWER = 11
Else
FINDANSWER = "neither 11 nor 12"
End If
End Function
You aren't limited to 7 nested if statements like you are when you are using the if function in a cell. I find that it is also less confusing to tell what you've done when the formula is as complicated as the one you have listed!

20010123, 21:25 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Limit to Nested Booleans? XL97
I'm fair on formulas but a moron on macros. Perhaps a better selfdocumented* way to do this would be to setup a table of allowed and disallowed combinations and address it using =index(). But I wanted to go for the single cell answer.
Parentchild formulas cured it.
*Better self documented to any poor sod who ever needs to understand what I'm doing.John ... I float in liquid gardens
UTC 7ąDS

20010123, 21:48 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Limit to Nested Booleans? XL97
You are right Jon, it blows up on the OR, when the NOT is inserted. So the NOT pushes the OR to the 8th level, not allowed. Can you explain how to count the nested levels?
Restructured it to parent & child formulas.John ... I float in liquid gardens
UTC 7ąDS

20010124, 00:42 #10
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia
 Posts
 85
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Limit to Nested Booleans? XL97
The levels in your formula are (I think) as shown below. Essentially you add one every time you add a function, and take off one every time you have a right bracket. If you get to more than 7, then <font face="Comic Sans MS">Kaboom!</font face=comic>
Jon
=
1) IF(
2) AND(B1=8,
3) OR(Input!E41="W",Input!E41="L")),N5,
2) IF(
3) NOT(
4) OR(Input!E41="W",Input!E41="L",Input!E41="N")),N13 ,
3) IF(B3<120,O13,
4) IF(
5) AND(Input!E41="L",
6) OR(Input!E37="B",Input!E37="F")),P13,
5) IF(
6) AND(Input!E41="W",
7) OR(Input!E38="M",Input!E38="F")),Q13,
6) IF(
7) AND(Input!E41="L",
8) NOT(
9) OR(Input!E38="M",Input!E38="F"))),,R13))))))

20010124, 11:13 #11calacucciaGuest
Re: Limit to Nested Booleans? XL97
If I'm not wrong, you can write this function which will work and does exactly the same:
=IF(AND(B1=8,OR(Input!E41="W",Input!E41="L")),N5,I F(OR(Input!E41="W",Input!E41="L",Input!E41="N"),IF (B3<120,O13,IF(AND(Input!E41="L",OR(Input!E37="B", Input!E37="F")),P13,IF(AND(Input!E41="W",OR(Input! E38="M",Input!E38="F")),Q13,IF(AND(Input!E41="L",I nput!E38<>"M",Input!E38<>"F"),,R13)))),N13))

20010124, 17:11 #12pccGuest
Re: Limit to Nested Booleans? XL97
John,
I have come across problems when cell contents exceed 256 characters, your formula contains more than 256. There is the 7 Boolean limit as well. I know from the 'old' days the any directory tree that contained names totalling more than 256 characters created problems for both DOS and windows.

20010124, 17:29 #13
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Limit to Nested Booleans? XL97
That was the limit in 123 for DOS. Without researching, I recall that the character limit per cell for Excel 5 & higher moved to 2056.
I quickly found one formula in one of my sheets which works fine with 709 characters. (See this thread).John ... I float in liquid gardens
UTC 7ąDS

20010124, 22:09 #14
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Limit to Nested Booleans? XL97
I appreciate you trying to help me with this. However, in your suggestion, this piece of the logic will always return FALSE:
AND(Input!E41="L",Input!E38<>"M",Input!E38<>"F")
because the value in cell Input!E38 cannot be both "M" and "F". How do I know? 'Cos I had already tried it!John ... I float in liquid gardens
UTC 7ąDS

20010124, 23:02 #15calacucciaGuest
Re: Limit to Nested Booleans? XL97
John,
Look good at the formula. Intentionally reverted the original
Not(Or(Input!E38="M",Input!E38="F")) to And(Input!E38<>"M",Input!E38<>"F")
This eliminated one nesting level, and is identical.
What the formula says is that the condition is covered if Input!E38 is NOT equal to M and F, which can of course be the case.