Results 1 to 7 of 7
Thread: logic help (2000)

20020611, 14:13 #1
 Join Date
 Jun 2002
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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"
Thankyou in advance!
thedinger...

20020611, 22:55 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,196
 Thanks
 2
 Thanked 460 Times in 379 Posts
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 121 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 1820 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.Cheers,
Paul Edstein
[MS MVP  Word]

20020612, 10:18 #3
 Join Date
 Jun 2002
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
I've developed a "tracking" database in QuattroPro. Yes, I realize that a spreadsheet is not the place to be developing databases, but my organization is bent on using the Corel office, and I'm not a big fan of Paradox (I'm more comfy with MS Access & FoxPro). I need to keep track of the current testing status of about 640 files, so I'm only developing 640 records in this spreadsheet, which (I feel) is well within the capabilities of QuattroPro. These 640 files go thru different levels of QC testing ( I call it 'making them jump thru certain hoops'). If a file passes thru a hoop, it moves onto the next level of testing. So my spreadsheet consists of columns that indicate if the file passed thru a certain hoop successfully or not. A requirement that was asked of me was to be able to determine the current status of a file, so I developed a "current status" column, and created the monster logic formula that you provided advice for yesterday. This formula looks at all of the "hoops" to see if the file passed thru them or not, and then tells me if it's waiting to jump thru the next hoop or if it failed while attempting to jump thru a certain hoop. Pretty basic stuff. I have the logic formula pointing to cell references on a sheet I've named SL, which is short for Status Legend. So lets assume that one of my files fails while attempting to jump thru the third hoop; I've placed a threeletter status code on the SL sheet so that the logic formula can point to it if need be. I'm using this pointing method because I'd like to be able to change the threeletter code to anything I want, at will, instead of using the find&replace feature. Finally, in my logic formula, I'm pointing to a couple of different sheets.....the Status Legend sheet, and the Location Legend sheet. So that's what the $SL and the $LL represent. I had to make the reference absolute because QuattroPro freaks out if you move the sheets around within the workbook. I'm pretty sure Excel doesn't freak if you move sheets around. If you've read down this far, I commend you for being patient with me and reading my jibberish! It's too bad that I don't have any VB experience, because I could break out of this limitation "thing" altogether. I'll be looking at my formula for redundancy, as you'd suggested, and go from there within Excel. Again, thank you for providing advice!
thedinger....

20020612, 11:46 #4
 Join Date
 Jan 2001
 Location
 Chelsea, Gtr London, United Kingdom
 Posts
 587
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: logic help (2000)
A passing thought this...
IMHO Why not break your formula down and use intermediate columns and 'subformulas'?
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.Steve H
IT Lecturer/Access Developer
O2K SR3/O2010; Win7Pro

20020612, 12:52 #5
 Join Date
 Jun 2002
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
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....

20020612, 19:43 #6
 Join Date
 Dec 2000
 Location
 Queanbeyan, New South Wales, Australia
 Posts
 3,730
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Subway Belconnen home of the Signboard to make you smile. Get (almost) daily updates follow SubwayBelconnen on Twitter.

20020612, 22:29 #7
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)