Results 1 to 7 of 7
  1. #1
    New Lounger
    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...

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 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 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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    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 three-letter 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 three-letter 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....

  4. #4
    4 Star Lounger SteveH's Avatar
    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 '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.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    New Lounger
    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....

  6. #6
    Platinum Lounger
    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.

  7. #7
    3 Star Lounger
    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)

Posting Permissions

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