Nested function limit

[Scott]

I've noticed that the limit for nested IF statements is
8. I've also noticed this is true for other functions
than IF as well. Is this true for all function? The only
way I can get around it is to put some of the inside
stuff in another cell, and the reference it. Any ideas?
Scott

 

[Frank Kabel]

Hi
this limit applies to all formulas (only 7 nested formulas are
allowed). For IF clauses you may create a lookup table and use VLOOKUP
to get the respective value for a search criteria

 

[Scott]

Okay, but most of my IF's refer to the cells around them,
and those references change accordingly down the column.
I think I'm just going to have to insert another column,
and have each do half of the work, as I don't think the
table applies here, unless you think it does.
Scott

 

[Andy Wiggins]

One of the more "entertaining" limits is with SUM. It's supposed to be
limited to 30 arguments. This workbook shows you that it isn't!

http://www.bygsoftware.com/examples/zipfiles/FunWithSum.zip
It's in the "Worksheet" section on page:
http://www.bygsoftware.com/examples/examples.htm


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Frank Kabel]

Hi

Okay, but most of my IF's refer to the cells around them,
and those references change accordingly down the column.
I think I'm just going to have to insert another column,
and have each do half of the work, as I don't think the
table applies here, unless you think it does.

You're right: In this case the table solution probably won't work. You
may have a look if you can combine criteria or arrange them differently
(depends on your specific issue). So if you like, post your specific
problem and maybe we can find a shorter/better solution

Frank

 

[JE McGimpsey]

As an "entertainment", your example is interesting. For real world use,
it is, IMO, of no utility.

Your explanation that

An extra set of brackets around all the arguments
makes SUM think of them as one argument.

is only true if the arguments within the parentheses refer to ranges.
Replace one of the range references with a constant or a function, and I
get a "This formula contains an error" message. Replace one of the range
references with a named constant or a range reference from a different
worksheet, and the formula returns #VALUE!

So it's not simply a matter of making "SUM think of them as one
argument".

When using the technique with a function can have 3 different results
for 3 otherwise valid input arguments, it makes the technique unusable
in any application that needs to be auditable/traceable.

In addition, while unlikely, using that type of non-standard technique
may not be supported in future versions of XL.

But it's a neat trick.

 

[Harlan Grove]

Your explanation that


is only true if the arguments within the parentheses refer to ranges.

....

Semantics! Well, in this case syntax.

The 'extra' set of parentheses makes them *one* multiple area range
reference. It's a semantic anomaly that the multiple areas may overlap. In
any case, it's no mystery nor any ingenious discovery that such multiple
area range references give the superficial impression of multiple arguments.

And it's not just SUM that 'thinks of them as one argument'. All functions
that can accept general range references (so including COUNT and NPV, but
not including SUMIF or COUNTIF) accept multiple area ranges.