need a help for abcd pattern tendencyPrev TopicNext Topic

• sysp34

  

  Heroic City
  Indonesia
  Member #31,688
  February 2, 2006
  1,153 Posts
  Offline
  Feb 25, 2006, 2:32 pm

  ▲▼

  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

  

  25

  New Jersey
  United States
  Member #17,842
  June 28, 2005
  180,983 Posts
  Offline
  Feb 25, 2006, 3:37 pm

  ▲▼

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

  

  Native American Eagle Sun

  MN
  United States
  Member #21
  December 7, 2001
  4,811 Posts
  Online
  Feb 26, 2006, 3:34 am

  ▲▼
  Quote: Originally posted by sysp34 on February 25, 2006

  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.

  The One Over None
  I Know...
• sysp34

  

  Heroic City
  Indonesia
  Member #31,688
  February 2, 2006
  1,153 Posts
  Offline
  Feb 26, 2006, 7:15 am

  ▲▼

  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

  

  East of Atlanta
  United States
  Member #6,191
  August 11, 2004
  1,390 Posts
  Offline
  Mar 3, 2006, 1:36 pm

  ▲▼

  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.