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
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

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?

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

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

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.

 Page 1 of 1