Welcome Guest
Log In | Register )
You last visited December 8, 2016, 8:49 pm
All times shown are
Eastern Time (GMT-5:00)

need a help for abcd pattern tendency

Topic closed. 4 replies. Last post 11 years ago by SirMetro.

Page 1 of 1
PrintE-mailLink
sysp34's avatar - Lottery-062.jpg
Heroic City
Indonesia
Member #31689
February 2, 2006
1153 Posts
Offline
Posted: February 25, 2006, 2:32 pm - IP Logged

dear master spreadsheet at LP member

please guide me to create abcd pattern tendency, i'm trying to create abcd function it work for single function but when i try to merge in one column it won't work.

please help me.

column
e4 for 1st
f4 for 2nd
g4 for 3rd
h4 for 4th

=IF(AND(e4=f4,e4=g4,e4=h4,f4=g4,f4=h4,g4=h4),"AAAA",
IF(AND(e4=f4,e4=g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"AAAB",
IF(AND(e4=f4,e4<>g4,e4=h4,f4<>g4,f4=h4,g4<>h4),"AABA",
IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"AABB",
IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"AABC",
IF(AND(e4<>f4,e4=g4,e4=h4,f4<>g4,f4<>h4,g4=h4),"ABAA",
IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABAB",
IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"ABAC",<<the and function won't work
IF(AND(e4<>f4,e4<>g4,e4=h4,f4=g4,f4<>h4,g4<>h4),"ABBA",
IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4=h4,g4=h4),"ABBB",
IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"ABBC",
IF(AND(e4<>f4,e4<>g4,e4=h4,f4<>g4,f4<>h4,g4<>h4),"ABCA",
IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABCB",
IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"ABCC","ABCD"
IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"ABCD"," "))))))))))))))

single row
=IF(AND(e4=f4,e4=g4,e4=h4,f4=g4,f4=h4,g4=h4),"AAAA",IF(AND(e4=f4,e4=g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"AAAB",IF(AND(e4=f4,e4<>g4,e4=h4,f4<>g4,f4=h4,g4<>h4),"AABA",IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"AABB",IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"AABC",IF(AND(e4<>f4,e4=g4,e4=h4,f4<>g4,f4<>h4,g4=h4),"ABAA",IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABAB",IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"ABAC",IF(AND(e4<>f4,e4<>g4,e4=h4,f4=g4,f4<>h4,g4<>h4),"ABBA",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4=h4,g4=h4),"ABBB",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"ABBC",IF(AND(e4<>f4,e4<>g4,e4=h4,f4<>g4,f4<>h4,g4<>h4),"ABCA",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABCB",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"ABCC","ABCD"))))))))))))))


thanks

sysp34

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    49784 Posts
    Offline
    Posted: February 25, 2006, 3:37 pm - IP Logged

    You need an equal number of close parentheses to your open parentheses?

      JADELottery's avatar - MeAtWork 03.PNG
      The Quantum Master
      West Concord, MN
      United States
      Member #21
      December 7, 2001
      3675 Posts
      Offline
      Posted: February 26, 2006, 3:34 am - IP Logged

      dear master spreadsheet at LP member

      please guide me to create abcd pattern tendency, i'm trying to create abcd function it work for single function but when i try to merge in one column it won't work.

      please help me.

      column
      e4 for 1st
      f4 for 2nd
      g4 for 3rd
      h4 for 4th

      =IF(AND(e4=f4,e4=g4,e4=h4,f4=g4,f4=h4,g4=h4),"AAAA",
      IF(AND(e4=f4,e4=g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"AAAB",
      IF(AND(e4=f4,e4<>g4,e4=h4,f4<>g4,f4=h4,g4<>h4),"AABA",
      IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"AABB",
      IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"AABC",
      IF(AND(e4<>f4,e4=g4,e4=h4,f4<>g4,f4<>h4,g4=h4),"ABAA",
      IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABAB",
      IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"ABAC",<IF(AND(e4<>f4,e4<>g4,e4=h4,f4=g4,f4<>h4,g4<>h4),"ABBA",
      IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4=h4,g4=h4),"ABBB",
      IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"ABBC",
      IF(AND(e4<>f4,e4<>g4,e4=h4,f4<>g4,f4<>h4,g4<>h4),"ABCA",
      IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABCB",
      IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"ABCC","ABCD"
      IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"ABCD"," "))))))))))))))

      single row
      =IF(AND(e4=f4,e4=g4,e4=h4,f4=g4,f4=h4,g4=h4),"AAAA",IF(AND(e4=f4,e4=g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"AAAB",IF(AND(e4=f4,e4<>g4,e4=h4,f4<>g4,f4=h4,g4<>h4),"AABA",IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"AABB",IF(AND(e4=f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"AABC",IF(AND(e4<>f4,e4=g4,e4=h4,f4<>g4,f4<>h4,g4=h4),"ABAA",IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABAB",IF(AND(e4<>f4,e4=g4,e4<>h4,f4<>g4,f4<>h4,g4<>h4),"ABAC",IF(AND(e4<>f4,e4<>g4,e4=h4,f4=g4,f4<>h4,g4<>h4),"ABBA",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4=h4,g4=h4),"ABBB",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4=g4,f4<>h4,g4<>h4),"ABBC",IF(AND(e4<>f4,e4<>g4,e4=h4,f4<>g4,f4<>h4,g4<>h4),"ABCA",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4=h4,g4<>h4),"ABCB",IF(AND(e4<>f4,e4<>g4,e4<>h4,f4<>g4,f4<>h4,g4=h4),"ABCC","ABCD"))))))))))))))


      thanks

      sysp34

      You could have too many nested functions.

      I think the limit is 7.

      Presented 'AS IS' and for Entertainment Purposes Only.
      Any gain or loss is your responsibility.
      Use at your own risk.

      Order is a Subset of Chaos
      Knowledge is Beyond Belief
      Wisdom is Not Censored
      Douglas Paul Smallish
      Jehocifer

        sysp34's avatar - Lottery-062.jpg
        Heroic City
        Indonesia
        Member #31689
        February 2, 2006
        1153 Posts
        Offline
        Posted: February 26, 2006, 7:15 am - IP Logged

        thanks doug

        i just knew the nested limitation

        Nested IF Function/Formula Nesting Limit Limitation


        One limitation of Excel is that we can only nest formulas up to 7 levels. This is particularly limiting when trying to add nested IF Functions/Formulas that require greater than 7 conditions.

        i guess it "fix" with new MSO 12

        and the abcd pattern tendency had solve with simple vlookup, although it take more size.

        thanks
        ragwi

          SirMetro's avatar - center
          East of Atlanta
          United States
          Member #6191
          August 11, 2004
          1389 Posts
          Offline
          Posted: March 3, 2006, 1:36 pm - IP Logged

          Perhaps I do not fully understand the question, but in case I do, I use to use the following;

          First, I would reduce the 4 digit number set to it's least amount (example 4321 would reduce to 1234) with the following formula.

          =CONCATENATE((SMALL(D2:G2,1)),(SMALL(D2:G2,2)),(SMALL(D2:G2,3)),(SMALL(D2:G2,4)))

          {the SMALL function will look at a group of numbers and then return the number per the position listed, a four digit number would use 1 thru 4. Because the 4 cells being referenced holds the number for each position, it makes the formula simple to deal with}

          Then, I had I formula that would test to see if the current number match the number being looked for. Once all of these numbers have been tested, I would cut & paste (use the "paste special function, DATA ONLY) the data to a new sheet and then sort the list. This gives me a complete and simple list of what followed the number.

          Also, you simply are trying to do too much in a single statement. Divide and conquer. Keep it simple. The more complex you make it, the harder it will be to understand. Just my thoughts.

          Sir Metro

          PS:

          If you send an EMail to sirmetro@yahoo.com , I will be happy to send to you the spreadsheet I used to find the followers for Cash 4 numbers. Its outdated, but will work easily if you simply update it.