Limit to Nested Booleans? XL97

Topic

New Reply

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,IF(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",NOT(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!)

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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[/url%5D

Reply | Quote

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.

Reply | Quote

glad to know i had the number right at least it was blowing up for me on the OR too.

Reply | Quote

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.

Reply | Quote

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 Kaboom!

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote