Multiple IF statements in equation
 

Hello,

I have this simple equation in excel where I have tested the individual
segments - I know it works, but when I put it into excel, it tells me there's
an error. If anyone has ideas, let me know - Thanks.

Below is the equation

=IF (C570, IF(C590, ((C59/C57)^(0.5))-1, IF (C57<0, IF(C59<0,
(((C59/C57)^(0.5))-1)*-1, IF(C57<0, IF(C590, (((ABS(C57) + ABS(C57) + C59)/
ABS(C57) ) ^0.5)-1, IF(C570, IF(C59<0, ((((C57 + C57 + ABS(C59)) /C57) ^0.5)
-1) *-1, ""))))))))

Multiple IF statements in equation
 

If you are in Excel 2003 or earlier, you have too many levels of
parentheses; they are nested too deeply. You need to rewrite the
formula such that it uses few levels of nested parentheses or you need
to split the formula in two (or more) cells.

Excel 2007 supports up to 64 levels of parents.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 29 Jan 2010 12:54:01 -0800, WA
wrote:


=IF (C570, IF(C590, ((C59/C57)^(0.5))-1, IF (C57<0, IF(C59<0,
(((C59/C57)^(0.5))-1)*-1, IF(C57<0, IF(C590, (((ABS(C57) + ABS(C57) + C59)/
ABS(C57) ) ^0.5)-1, IF(C570, IF(C59<0, ((((C57 + C57 + ABS(C59)) /C57) ^0.5)
-1) *-1, ""))))))))

Multiple IF statements in equation
 

If you are using Excel 2003, it looks as if you've exceeded the limit on
nesting level (7).

If you use Excel 2007 to avoid that limit, it looks as if much of the
formula is unreachable. You need to look at it a stage at a time and see
for each IF condition which parts of the formula are executed if the
condition is met, and which if the condition is not met. Your first IF test
is for C570, and there is no part which is executed if that condition is
not met. Hence your 3rd IF test, for C57<0, is pointless because you are
still in the part which is only executed if C570. Similarly at other
stages of the formula breakdown. If you say you know it works, I think you
might be confusing yourself. Look at it again.

Perhaps what you wanted was something like:
=IF(C570,IF(C590,((C59/C57)^0.5)-1,
IF(C59<0,((((C57+C57+ABS(C59))/C57)^0.5)-1)*-1, "")),
IF(C57<0,IF(C59<0,(((C59/C57)^0.5)-1)*-1,
IF(C590,(((ABS(C57)+ABS(C57)+C59)/ABS(C57))^0.5)-1, "")), "")) ?
--
David Biddulph

"WA" wrote in message
...
Hello,

I have this simple equation in excel where I have tested the individual
segments - I know it works, but when I put it into excel, it tells me
there's
an error. If anyone has ideas, let me know - Thanks.

Below is the equation

=IF (C570, IF(C590, ((C59/C57)^(0.5))-1, IF (C57<0, IF(C59<0,
(((C59/C57)^(0.5))-1)*-1, IF(C57<0, IF(C590, (((ABS(C57) + ABS(C57) +
C59)/
ABS(C57) ) ^0.5)-1, IF(C570, IF(C59<0, ((((C57 + C57 + ABS(C59)) /C57)
^0.5)
-1) *-1, ""))))))))