1. ## logic help (2000)

I've recently migrated from QuattroPro9 to Excel2000. I have a HUGE logic formula in Quattro that I want to migrate over to Excel. I've been fiddling around for about 2 hours with this thing, and I'm not sure what I'm doing wrong. I realized that Excel won't allow more than 7 nested levels in an if statement, so I was a tad let down. But I figured that hey, if Quattro can do this, then there's got to be a way to do it with Excel!!!!. Here's the formula (I've placed a hard return after each logical statement for ease of reading), please offer advice:
@IF(L2="nps"#AND#N2="NUL",\$SL:\$A\$8,
@IF(L2="nps"#AND#p2="n"#and#r2="n"#and#t2="n",\$SL: \$A\$4,
@IF(L2="RFP"#AND#S2="y"#AND#T2=""#AND#P2<>"y",\$SL: \$A\$15,
@IF(L2="RFP"#AND#P2="y",\$SL:\$A\$13,
@IF(L2="RFP"#AND#T2="n",\$SL:\$A\$10,
@IF(L2="RFP"#AND#T2="y",\$SL:\$A\$14,
@IF(S2="WQA",\$SL:\$A\$16,
@IF(L2="PA1",\$SL:\$A\$9,
@IF(N2="y",\$SL:\$A\$5,
@IF(L2="CER"#AND#T2="n",\$SL:\$A\$4,
@IF(L2="CER"#AND#T2="y",\$SL:\$A\$12,
@IF(L2<>""#AND#S2="y"#AND#T2=""#AND#P2<>"y",\$SL:\$A \$15,
@IF(L2="FLC"#AND#T2="n",\$SL:\$A\$4,
@IF(L2="FLC"#AND#T2="y",\$SL:\$A\$12,
@IF(L2<>""#AND#P2=""#AND#N2="n",\$SL:\$A\$20,
@IF(L2="CER"#AND#K2="SN1"#and#m2="n",\$LL:\$A\$5,
@IF(L2="FLC"#AND#K2="SN1"#and#m2="n",\$LL:\$A\$5,
@IF(L2="FQA"#AND#R2="y",\$SL:\$A\$6,
@IF(L2="CER"#AND#R2="y",\$SL:\$A\$6,
@IF(L2="FLC"#AND#R2="y",\$SL:\$A\$6,
@IF(L2="FLC"#AND#P2="y",\$SL:\$A\$3,
@IF(L2="CER"#AND#P2="y",\$SL:\$A\$3,
@IF(L2="FQA"#AND#P2="n"#AND#R2="n"#AND#T2="n",\$SL: \$A\$4,
@IF(L2="FQA"#AND#P2="n"#AND#R2="n"#AND#T2="y",\$SL: \$A\$12,
@IF(L2="FQA"#AND#P2="y",\$SL:\$A\$3,
"error"

thedinger...

2. ## Re: logic help (2000)

While it's true that you can only nest 8 if statements, you can have more than 7 if statements in the same formula. For example put this formula in A2 and any number in A1:
=IF(OR(A1<1,A1>21),"ERROR",IF(A1=1,"A",IF(A1=2,"B" ,IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",I F(A1=7,"G","")))))))&IF(A1=8,"H",IF(A1=9,"I",IF(A1 =10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF( A1=14,"N","")))))))&IF(A1=15,"O",IF(A1=16,"P",IF(A 1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF (A1=21,"U",""))))))))
What this formula does is test the value in A1 to see if it's in the range of 1-21 and report the corresponding letter of the alphabet, or "ERROR" if outside the designated range. The way it's done is by having multiple if statements nested down to 7 levels returning nulls if the value in A1 is outside their range. These are then joined by "&" within an encompassing if statement. I'm not saying this is ideal, but it's one way of tackling your problem.

A number of your if statements are also testing overlapping criteria and can be simplified. For example, rows 18-20 could be reduced to something like:
if(and(or(L2="FQA",L2="CER",L2="FLC"),R2="y")),\$A\$ 6 (I'm not sure what the \$SL: bit in your formula is about)

Depending on whether there's a strict heirarchy required, you could possibly take the same approach with the formulae on rows 2, 10, 13, 23, which all reference \$A\$4, and so on.

3. ## Re: logic help (2000)

Thank you very much for the advice, especially with pointing out the overlapping logic statements. You'd also mentioned that you weren't sure about what \$SL represented. I'll explain what I'm doing in my work to shed some light on the the methods of my madness.

thedinger....

4. ## Re: logic help (2000)

A passing thought this...
IMHO Why not break your formula down and use intermediate columns and 'sub-formulas'?
Hide them away when you get it all working.
It would be much easier to debug and probably to update as well and will get round the limitation on nested statements.

5. ## Re: logic help (2000)

That's a great idea, and I'd considered it when I first began taking on this task, but threw out the idea because I only wanted to have to fiddle with ONE formula, instead of several smaller formulae. However, you suggested the idea, and I'm going to reconsider. Thankyou very much for provoking thought!

thedinger....

6. ## Re: logic help (2000)

If you're comfortaable with Access, then develop a solution in VBA. It will be a lot easier to maintain; and, if you use the formula in a lot of places, will lead to a much smaller workbook.

7. ## Re: logic help (2000)

Consider a macro using Case (XL2000 VBA is quite good on the Select Case Statement), I dont know if Case has a limit, but it is certainly greater than the 7 nested IFs)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•