# Thread: Limit to Nested Booleans? XL97

1. ## 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 single-cell this one.

(As to the formula, don't ask!)

2. ## 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

3. ## 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.

4. ## 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.j-walk.com

5. ## 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.

6. ## 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.

7. ## Re: Limit to Nested Booleans? XL97

I suggest creating a function to compute your desired results.

A simple example of a function would be:

A B C
2

In your visual basic module (insert module)

If ITEM1 = 1 And ITEM2 = 2 Then
ElseIf ITEM1 = 1 And ITEM2 = 1 Then
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!

8. ## Re: Limit to Nested Booleans? XL97

I'm fair on formulas but a moron on macros. Perhaps a better self-documented* 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.

Parent-child formulas cured it.

*Better self documented to any poor sod who ever needs to understand what I'm doing.

9. ## 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.

10. ## 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))))))

11. ## 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))

12. ## 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.

13. ## 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).

14. ## 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!

15. ## 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•