Whats wrong with this formula?????

[Jen Hearns]

First of all, I want to thank you guys, you are great!! I have this
formula in a cel, and as soon as I add the last line, it comes up
saying my formula has an error.


=IF(AND(C3=0,C11=1),C7+(I7*2),
IF(AND(C3=1,C11=1),L2+(I7*2),
IF(AND(C3=0,C11=2),C7+(I7*2),
IF(AND(C3=2,C11=2),L2+(I7*2),
IF(AND(C3=0,C11=4),C7+(I7*2),
IF(AND(C3=4,C11=4),L2*2+(I7*2),
IF(AND(C3=0,C11=44),C7+(I7*2),
IF(AND(C3=44,C11=44),c7+(i7*2))))))))

am I past the amount of formulas, or characters I am allowed to use?

 

[Paul Corrado]

Jen

You have exceeded the maximum number of nested IF functions, 7. Plus I
think you have more arguments then you need. It looks like you are saying
if C3=C11 use L2 and if C3<>C11 use C7

=(C3<>C11)*(C7)+(C3=C11)*(L2)+(i7*2)

Should work

HTH

PC

 

[mike]

I believe you are past the amount of conditions excel will
allow the help section states:

"You can enter, or nest, no more than seven levels of
functions within a function."

However I have been able to get away with 8 levels which
you have so I really am not helping you am I?

 

[Mark Graesser]

You can get aroud the limit of 7 nested IF's by not nesting. Try:

=IF(AND(C3=0,C11=1),C7+(I7*2),0)+IF(AND(C3=1,C11=1),L2+(I7*2),0)+IF(AND(C3=0
,C11=2),C7+(I7*2),0)+IF(AND(C3=2,C11=2),L2+(I7*2),0)+IF(AND(C3=0,C11=4),C7+(
I7*2),0)+IF(AND(C3=4,C11=4),L2*2+(I7*2),0)+IF(AND(C3=0,C11=44),C7+(I7*2),0)+
IF(AND(C3=44,C11=44),C7+(I7*2),0)

Each IF statement will produce zero except for the one which is true. The
formula then adds them all up. Since the IF's aren't nested there is no
limit that I have run into.

Good Luck,
Mak Graesser

 

[Gord Dibben]

You can have only seven(7) nested IF's.

For a work-round see Chip Pearson's site.

http://www.cpearson.com/excel/nested.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002