Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    When is it a nest! (2003)

    I was confronted with this question a couple of days ago, and I think I got a little confused myself with the answer. Since then I have come across a couple of answers that all seem to be contradictory to some extent.
    For example:
    1. The best way to determine the amount of nested fucntions is to count the amount of closing parenthases "))))".
    2. It is not a nested function if it is used as an argument in another function!
    3. A nest occurs when you have more than one function name after the other... eg. =If(ISERROR(VLOOKUP(SUM(.....

    Can anyone clarify exactly when a function counts as a nested function and when it is not seen as a nested function? Its all very confusing!
    TX
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: When is it a nest! (2003)

    1 and 3 don't appear contradictory to me. You can count opening/closing parentheses, but you'd have to exclude parentheses that are used to group arguments - for example the parentheses in (A2+B2)*C2 don't constitute a nesting level. Taking this into account, you could start from the left, and add 1 to the running count for each opening parenthesis, and subtract 1 for each closing parenthesis you encounter. The running count is the nesting level. You can also start from the right, and add 1 for each closing parenthesis, and subtract 1 for each opening parenthesis you encounter.

    I don't understand 2.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When is it a nest! (2003)

    Have a look at these two examples. In each case there are more than 7 functions, but the functio still works. So by counting brackets or even the Name of the Function does not really tell how many nests there are??

    =IF(ISNUMBER(C2),IF(IF(MID(B2,FIND(" ",B2,1)+1,LEN(B2)-FIND(" ",B2,1))="Blend",C2*0.5,C2) /Portfolio> 0.25,"Check Diversification","Leave it alone"), "Investment isn't a number.")

    =IF(OR(F12=1,F12=2),1,IF(F12=3,0.8,IF(F12=4,0.7,IF (F12=5,0.6,IF(F12=6,0.5,IF(F12=7,0.4,IF(F12=8,0.35 ,IF(F12=9,0.3,"0"))))))))

    I am not trying to argue this point. Usually I never get to such a "rats"nest anyway, but its simply trying to understand the correct context of what constitutes a nested function??...
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: When is it a nest! (2003)

    Nesting is the function within the function. If I understand them correctly.

    The first example seems to have only 4 nests
    =IF(
    1) ISNUMBER(C2), IF(
    2) IF(
    3) MID(B2,
    4) FIND(" ",B2,1)+1,LEN(B2)-FIND(" ",B2,1))

    And the second 7
    =IF(
    1) OR(F12=1,F12=2),1,IF(F12=3,0.8,
    2)IF(F12=4,0.7,
    3)IF(F12=5,0.6,
    4)IF(F12=6,0.5,
    5)IF(F12=7,0.4,
    6)IF(F12=8,0.35,
    7) IF(F12=9,0.3,"0"))))))))

    Notice that some "Nests" may have more than 1 function in it.. It is not the number of functions, but the number of distinct "levels" in the hierarchy (ie the "nest")

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: When is it a nest! (2003)

    The first formula doesn't go deeper then level 5, i.e. 4 levels nested within the top level, so it stays well within the limit. (Use the method I described earlier to determine the level)
    The second one goes to level 8, i.e. 7 levels nested within the top level, so it has the maximum number of nested functions.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: When is it a nest! (2003)

    >>>>Notice that some "Nests" may have more than 1 function in it.. It is not the number of functions, but the number of distinct "levels" in the hierarchy (ie the "nest")

    This statement is the one that explains all the confusion. It seems that some functions that are envolved in the argument of the outer function are sometimes not counted as a nest.
    I think that it is also possible to see the "nesting" more clearly if you run the Evaluate Formula in the Formula Auditing Command.

    Thanx again Hans and Steve.
    Cheers
    Regards,
    Rudi

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: When is it a nest! (2003)

    <hr>It seems that some functions that are envolved in the argument of the outer function are sometimes not counted as a nest.<hr>

    They are counted, but they may be in the same nest as another as I indicated in the numbering. Some nests in your example had 2 functions, others had 3.

    Steve

Posting Permissions

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