I have not gotten an answer yet from the yahoo MS Excel group so I thought I could try here.
So I tried to input this cascaded IF statement into excel and it gave me the following message below:
=IF(AND(M2=0,L2>0),"IBO",IF(AND(L2=0,M2<>0),"PBO",IF(AND(L2=0,M2=0),"ZERO BALANCE",IF(AND(L2>0,M2=-L2),"NON-BALANCED",IF(AND(L2>0,M2=L2),"IB PB EQUAL", IF(AND(Q2<>L2,Q2<>M2,OR(Q2=M2,R2=M2,S2=M2,T2=M2,U2=M2,V2=M2,W2=M2)),"BOTH PB EXCEPTION",IF(AND(Q2<>M2,Q2<>L2,OR(Q2=L2,R2=L2,S2=L2,T2=L2,U2=L2,V2=L2,W2=L2)),"BOTH IB EXCEPTION","")))))))&IF(AND(OR(P2=Q2,L2=Q2),OR(P2=Q2,M2=Q2)),"BOTH SAB INS/PAT",IF(AND(N2="+",Q2<>P2,Q2<>L2,Q2<>M2),"BOTH IB+ PB+",IF(AND(N2="-",Q2<>P2,Q2<>L2,Q2<>M2),"BOTH IB+ PB-"," ")))
"The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format"
The IF statement above is only 10 levels deep, and Excel will not take it. Is there something wrong with my syntax? because in the very same spreadsheet, I have a nested IF that is 11 levels deep that completely works and Excel never gave me that error message.
The cascaded IF statement that completely works is as follows:
=IF(AND(M2=0,L2>0),Q2,IF(AND(Q2<>L2,Q2<>M2,OR(Q2=M2,R2=M2,S2=M2,T2=M2,U2=M2,V2=M2,W2=M2)),Q2,IF(AND(Q2<>M2,Q2<>L2,OR(Q2=L2,R2=L2,S2=L2,T2=L2,U2=L2,V2=L2,W2=L2)),0,IF(OR(P2=Q2,L2=Q2),L2,IF(OR(P2=Q2,M2=Q2),0,IF(AND(N2="+",Q2<>P2,Q2<>L2,Q2<>M2),((L2/P2)*Q2),IF(AND(N2="-",Q2<>P2,Q2<>L2,Q2<>M2),(-M2*(Q2/P2))+Q2,0)))))))+IF(AND(L2=0,M2<>0),0,IF(AND(L2=0,M2=0),0,IF(AND(L2>0,M2=-L2),0,IF(AND(L2>0,M2=L2),0,0))))
I know that I did this cascade a bit different, with the + and the 0's rather than the "" and the & but I figured I needed to do it this way because my outputs were text and opposed to numerical currency values. In all cases, (for both cascaded IFs above, the cells I am referencing are numerical currency values.
What did I do wrong? Can anyone fix this up for me?
Thank you in advance,
__._,_.___
Posted by: Barry White <imtigerwords@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar