Nesting Issues

[Joseph.Marro]

Hello again,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I swear this project is going to kill me! I have one super formula that has a group of nested functions. The formula is below and works as intended.<o></o>
<o></o>
=IF(A4="Y",IF(AND(OR(AU4="PASS",AU4="N/A"),OR(AX4="PASS",AX4="N/A")),IF(COUNTA(B4:C4,E4:J4,M4:N4)=10,IF(OR(AND(H4="UID2",ISBLANK(L4)),AND(E4="EMB",ISBLANK(Q4))),"FAIL: CONDITIONAL REQUIREMENTS NOT MET","INSERT NEXT FUNCTION"),"FAIL: MISSING REQUIRED FIELD"),"FAIL: ASSOCIATED RECORD FAILURE"),"INACTIVE")<o></o>
<o></o>
I wrote the next formula to be inserted at the IF FALSE section, labeled "INSERT NEXT FUNCTION' in the above formula. The formula below works as intended by itself.<o></o>
<o></o>
=IF(OR(AND(I4="LD",LEN(J4)=6,OR(LEFT(J4,1)="N",LEFT(J4,1)="V",LEFT(J4,1)="Q",LEFT(J4,1)="R"),ISNUMBER(RIGHT(J4,5)*1)),AND(I4="D",LEN(J4)=5)),"INSERT NEXT FUNCTION","FAIL: FIELD REQUIREMENTS NOT MET")<o></o>
<o></o>
However, when I combine the two I get an error as soon as I hit enter. The combined formula looks like this;<o></o>
<o></o>
=IF(A4="Y",IF(AND(OR(AU4="PASS",AU4="N/A"),OR(AX4="PASS",AX4="N/A")),IF(COUNTA(B4:C4,E4:J4,M4:N4)=10,IF(OR(AND(H4="UID2",ISBLANK(L4)),AND(E4="EMB",ISBLANK(Q4))),"FAIL: CONDITIONAL REQUIREMENTS NOT MET",IF(OR(AND(I4="LD",LEN(J4)=6,OR(LEFT(J4,1)="N",LEFT(J4,1)="V",LEFT(J4,1)="Q",LEFT(J4,1)="R"),ISNUMBER(RIGHT(J4,5)*1)),AND(I4="D",LEN(J4)=5)),"INSERT NEXT FUNCTION","FAIL: FIELD REQUIREMENTS NOT MET")),"FAIL: MISSING REQUIRED FIELD"),"FAIL: ASSOCIATED RECORD FAILURE"),"INACTIVE")<o></o>
<o></o>
The Formula is not greater than 1024 characters and less than 7 nested IF() statements. When I hit ok on the error screen the first LEFT() is highlighted. I don’t get why they work separately but not together. The second formula is just the IF FALSE portion of the previous IF() statement.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joe<o></o>
<o></o>
<o></o>

 

[MorganO]

This is not an issue with nested ifs. This is an issue with nested formulas. Excel 2003 can only support 7 levels of nested formulas (see this link for more info.) I beleive Excel 2007 supports more levels of nesting.

The reason the formula is failing at the Left functions is that it is the 8th nested function in your formula.

You are most likely going to have to re-write the formula to use two cells rather than one - half the formula in one cell the other half in the other.

Take care.

Owen

 

[lenze]

A workaround to the problem pointed out by Owen can be found here.

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

lenze