Nested IF statements - maxed out!

[RRDMAT]

I currently have the following formula in a cell:
IF(AND(J40=12,J41="YES"),"Multiple",IF(J39="","",IF(AND(B48="NO",J40=6),SUM(B39,B41,B43)*3,IF(B48="YES",SUM(B49:B52,B41,B43,B43,B45),IF(AND(B46="YES",D54="YES"),(((+B39+B41+B43)*2)+B45),IF(B46="YES",(((+B39+B41+B43)*4)+B45),IF(D54="YES",(((+B39+B41+B43)*2)+B45),(((+B39+B41+B43)*4)+B45+B45))))))))

I want to add another condition: IF(AND(J40=4,B48="YES"),(B50+B51)*2)

Any ideas on how to get past the limitation of 7? Maybe a better, more
condensed, way to write the formula?

 

[Trevor Shuttleworth]

I don't think you can get past the limitation, unless that's a feature in
Excel 2007

I can't really test this because I don't understand the logic and I don't
have the data. But, splitting it down, you see:

IF(AND(J40=12,J41="YES"),"Multiple",
IF(J39="","",
IF(AND(B48="NO",J40=6),SUM(B39,B41,B43)*3,
IF(B48="YES",SUM(B49:B52,B41,B43,B43,B45),
IF(AND(B46="YES",D54="YES"),(((+B39+B41+B43)*2)+B45),
IF(B46="YES",(((+B39+B41+B43)*4)+B45),
IF(D54="YES",(((+B39+B41+B43)*2)+B45),
(((+B39+B41+B43)*4)+B45+B45))))))))

It looks as though two conditions have the same result:

IF(AND(B46="YES",D54="YES"),(((+B39+B41+B43)*2)+B45),
IF(D54="YES",(((+B39+B41+B43)*2)+B45),

So, maybe you could combine them, removing one of the IF statements:

IF(OR(AND(B46="YES",D54="YES"),D54="YES"),(((+B39+B41+B43)*2)+B45),

I think that might mean the B46="YES" test is redundant but I'm not sure.

Anyway, if that works, you can add another IF statement.

I have to be honest and say that I think this will be a nightmare to check
and prove. And, in a few months time you'll probably have no idea what you
were trying to do. More to the point, neither will anyone else.

As an example, you seem to have some repetition of cells:

IF(B48="YES",SUM(B49:B52,B41,B43,B43,B45), 2 x B43
(((+B39+B41+B43)*4)+B45+B45)))))))) 2 x +B45

But, I'm sure you know what you're trying to do ... at least for the moment

Regards

Trevor

 

[Elkar]

Excel 2007 does increase the nesting limit to 64 levels. But, upgrading to
2007 may not be an option for you at this time.

Trevor's suggestion to eliminate redundancies is good advice, and may be
adequate for your immediate needs.

However, here's a trick to get around the nesting limitations in older
versions of Excel. You can concatenate multiple IF statements together,
rather than nesting inside of one another.

=IF(A1="A","TRUE","")&IF(A1=""B","TRUE","")&IF(A1="C","TRUE","") etc...

This will work as long as only 1 condition will ever evaluate to TRUE.
Also, the final result will be a text value, but if you need a number you can
enclose the entire thing in a VALUE() function.

Ok, hopefully that all makes sense.

HTH,
Elkar