Results 1 to 7 of 7
Thread: When is it a nest! (2003)

20061004, 09:31 #1
 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!
TXRegards,
Rudi

20061004, 10:12 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20061004, 10:59 #3
 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

20061004, 12:16 #4
 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

20061004, 12:16 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20061004, 12:45 #6
 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.
CheersRegards,
Rudi

20061004, 14:18 #7
 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