I'm working in Excel and I have formulas that may need as many as 14-16
functions. "HELP" says I can use only up to 7 functions. I know there have
been loger formulas written and used, how do I get around this conundrum?

On Thu, 17 Apr 2008 15:55:00 -0700, Dan wrote:

I'm working in Excel and I have formulas that may need as many as 14-16
functions. "HELP" says I can use only up to 7 functions. I know there have
been loger formulas written and used, how do I get around this conundrum

You have misunderstood what you read. HELP does not indicate that you cannot
*use* more than 7 functions. It indicates that you cannot *nest* more than 7
functions.

If you have a requirement to *nest* more than seven functions, your options
include:
Use different logic/functions so as to remove the requirement for
excessive nesting.
Use Excel 2007, which has a 64 level nesting limit.
Write a User Defined Function (UDF) in, for example, VBA.
--ron

Depending on exactly what you want to do, you may have a few options:

Try using a Pivot Table (sometimes these tings work as IF functions on
steroids)
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.contextures.com/xlPivot02.html

Also, consider using a sumproduct function:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This may give you what you want, again, depending on what you want to do.
As far as I know, the limit of conditions in a sumproduct function is 30.

Regards,
Ryan---



--
RyGuy


"Ron Rosenfeld" wrote:

On Thu, 17 Apr 2008 15:55:00 -0700, Dan wrote:

I'm working in Excel and I have formulas that may need as many as 14-16
functions. "HELP" says I can use only up to 7 functions. I know there have
been loger formulas written and used, how do I get around this conundrum

You have misunderstood what you read. HELP does not indicate that you cannot
*use* more than 7 functions. It indicates that you cannot *nest* more than 7
functions.

If you have a requirement to *nest* more than seven functions, your options
include:
Use different logic/functions so as to remove the requirement for
excessive nesting.
Use Excel 2007, which has a 64 level nesting limit.
Write a User Defined Function (UDF) in, for example, VBA.
--ron

Depending on exactly what you want to do, you may have a few options:

Try using a Pivot Table (sometimes these tings work as IF functions on
steroids)
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.contextures.com/xlPivot02.html

Also, consider using a sumproduct function:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This may give you what you want, again, depending on what you want to do.
As far as I know, the limit of conditions in a sumproduct function is 30.

Regards,
Ryan---


--
RyGuy


"Ron Rosenfeld" wrote:

On Thu, 17 Apr 2008 15:55:00 -0700, Dan wrote:

I'm working in Excel and I have formulas that may need as many as 14-16
functions. "HELP" says I can use only up to 7 functions. I know there have
been loger formulas written and used, how do I get around this conundrum

You have misunderstood what you read. HELP does not indicate that you cannot
*use* more than 7 functions. It indicates that you cannot *nest* more than 7
functions.

If you have a requirement to *nest* more than seven functions, your options
include:
Use different logic/functions so as to remove the requirement for
excessive nesting.
Use Excel 2007, which has a 64 level nesting limit.
Write a User Defined Function (UDF) in, for example, VBA.
--ron