Results 1 to 15 of 15
  1. #1
    Uranium Lounger
    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 single-cell this one.

    (As to the formula, don't ask!)
    -John ... I float in liquid gardens
    UTC -7ąDS

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

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

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

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

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

  7. #7
    New Lounger
    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!

  8. #8
    Uranium Lounger
    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 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.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

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

  11. #11
    calacuccia
    Guest

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

    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. #13
    Uranium Lounger
    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

  14. #14
    Uranium Lounger
    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

  15. #15
    calacuccia
    Guest

    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
  •