+ Reply to Thread
Results 1 to 22 of 22

help: counting number of permutations of 4 digits...

  1. #1
    Registered User
    Join Date
    09-16-2006
    Posts
    21

    help: counting number of permutations of 4 digits...

    need help... completely noobs...

    am trying to make a permutation that count a number of permutations of 4 digits which read out as digit or text and digit as for example below for each cells in my worksheet...

    eg:

    1234 = M24 = and set to $1.00 to calculate number of permutations = $24.00

    and then the other part of my worksheet cells...

    $1.00 + $1.00 = $2.00 then multiply "M24" = $48.00

    which obviously i know how to calculate and get the answer but the problem is the permutations...

    1234 M24
    1233 M12
    1212 M06
    1222 M04
    1111 DIRECT/NONE/NA/M01

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062
    Assuming all the values are 4 digits long, ie greater than or equal to 1000 formula based on M24 is

    =MID((TEXT(M24,"0")),1,1)*=MID((TEXT(M24,"0")),2,1)*=MID((TEXT(M24,"0")),3,1)*=MID((TEXT(M24,"0")),4,1)

    Then just copy the formula down the column
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    thanks, but that is not i really want to do with...

    please see my attachment ...
    Attached Images Attached Images

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by wann
    thanks, but that is not i really want to do with...

    please see my attachment ...
    The Wikipedia definition of "A permutation is an ordered sequence containing each symbol from a set once and only once" together the Dictionary .com definition including the words "<mathematics> 1. An ordering of a certain number of elements of a given set." would suggest that your 'should read' in the .jpg is not quite correct.

    If you were trying to generate the complete list, then put '1111 in cell A1 and in cell A2 the formula

    =IF(MID(A1,4,1)+0<9,LEFT(A1,3)&TEXT(MID(A1,4,1)+1,"0"),IF(MID(A1,3,1)+0<9,LEFT(A1,2)&TEXT(MID(A1,3,1)+1,"0")&"1",IF(MID(A1,2,1)+0<9,LEFT(A1,1)&TEXT(MID(A1,2,1)+1,"0")&"11",IF(LEFT(A1,1)+0<9,TEXT(LEFT(A1,1)+1,"0")&"111","end"))))

    and formula-fill that to cell A6562

    In cell B1 put

    =LEFT(A1,1)*MID(A1,2,1 )*MID(A1,3,1)*MID(A1,4,1)

    and formula-fill that down.

    that generates the full list of 'how many from 4 digits'

    I do not understand how you can have 'a permutation that count a number of permutations of 4 digits' and have '1010' as shown in rows 17 & 26 of your jpg, much less how two '1' digits can form 6 permutations.

    I presume that from the B column above that you can generate the required answers?

    hth
    ---

  5. #5
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    sorry... that is not exactly i wanted...

    1010 should permutate 6 set of numbers (1010,1001,1100,0110,0101,0011) no matter in what order the number that i need to key in either 0011 it's still give me a 6 sets of numbers.

    i'm not making a lists of permutate number but i need a formula when i key in a number it should give me an output of permutate numbers can be generate as in my .jpg

    hope everyone understand what i mean...



    Quote Originally Posted by Bryan Hessey
    The Wikipedia definition of "A permutation is an ordered sequence containing each symbol from a set once and only once" together the Dictionary .com definition including the words "<mathematics> 1. An ordering of a certain number of elements of a given set." would suggest that your 'should read' in the .jpg is not quite correct.

    If you were trying to generate the complete list, then put '1111 in cell A1 and in cell A2 the formula

    =IF(MID(A1,4,1)+0<9,LEFT(A1,3)&TEXT(MID(A1,4,1)+1,"0"),IF(MID(A1,3,1)+0<9,LEFT(A1,2)&TEXT(MID(A1,3,1)+1,"0")&"1",IF(MID(A1,2,1)+0<9,LEFT(A1,1)&TEXT(MID(A1,2,1)+1,"0")&"11",IF(LEFT(A1,1)+0<9,TEXT(LEFT(A1,1)+1,"0")&"111","end"))))

    and formula-fill that to cell A6562

    In cell B1 put

    =LEFT(A1,1)*MID(A1,2,1 )*MID(A1,3,1)*MID(A1,4,1)

    and formula-fill that down.

    that generates the full list of 'how many from 4 digits'

    I do not understand how you can have 'a permutation that count a number of permutations of 4 digits' and have '1010' as shown in rows 17 & 26 of your jpg, much less how two '1' digits can form 6 permutations.

    I presume that from the B column above that you can generate the required answers?

    hth
    ---

  6. #6
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    what i'm trying to do is to find a formula that can generate me a set of permutate numbers.

    maybe i've explain and ask the wrong question here that make you guys misunderstood what i'm trying to do here...

    only 4 digits or should i say 4 numbers 1 2 3 4 (1234) in 1 cell then a formula that count or should i say generate a set of permutate numbers on that cell not a lists of permutate numbers. am only want numbers that output me the total of permutate numbers either as 24 or M24 which stand for 24 set of numbers or 24 4 digits numbers.

    1234 have 24, 1233 have 12, 1212 have 6, 1211 have 4, and 1111 have no permutation as appear in my .jpg (previous post above)


    Quote Originally Posted by wann
    need help... completely noobs...

    am trying to make a permutation that count a number of permutations of 4 digits which read out as digit or text and digit as for example below for each cells in my worksheet...

    eg:

    1234 = M24 = and set to $1.00 to calculate number of permutations = $24.00

    and then the other part of my worksheet cells...

    $1.00 + $1.00 = $2.00 then multiply "M24" = $48.00

    which obviously i know how to calculate and get the answer but the problem is the permutations...

    1234 M24
    1233 M12
    1212 M06
    1222 M04
    1111 DIRECT/NONE/NA/M01

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    So you simply need a formula to state how many different characters are in the 4 digits so that you can set the 'M'?

    added (forgot to do this before going to cricket training)

    =1+IF(MID(A1,3,1)=MID(A1,4,1),0,1)+IF(ISERROR(FIND(MID(A1,2,1),MID(A1,3,2))),1,0)+IF(ISERROR(FIND(LEFT(A1,1),MID(A1,2,3))),1,0)


    (note, remove any spaces in the formula when you copy it)
    --

    Quote Originally Posted by wann
    what i'm trying to do is to find a formula that can generate me a set of permutate numbers.

    maybe i've explain and ask the wrong question here that make you guys misunderstood what i'm trying to do here...

    only 4 digits or should i say 4 numbers 1 2 3 4 (1234) in 1 cell then a formula that count or should i say generate a set of permutate numbers on that cell not a lists of permutate numbers. am only want numbers that output me the total of permutate numbers either as 24 or M24 which stand for 24 set of numbers or 24 4 digits numbers.

    1234 have 24, 1233 have 12, 1212 have 6, 1211 have 4, and 1111 have no permutation as appear in my .jpg (previous post above)
    Last edited by Bryan Hessey; 09-19-2006 at 05:39 AM.

  8. #8
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    Sorry Bryan, I'm still don't get what i'm expected from the formula given...

    Let me explain again in diffrent way...

    Example 1.
    Four different numbers in A1 (1234). In how many different ways could you arrange them?

    My answer in A2 24

    No matter what numbers or any form of numbers arrangement from 0000 to 9999 that I need to enter in A1...

    (1234) = 24 or M24
    (1233) = 12 or M12
    (1212) = 06 or M06
    (1211) = 04 or M04
    (1111) = 01 or M01 or STRAIGHT

    (6789) = 24
    (6988) = 12
    (1010) = 06
    (9000) = 04
    (0000) = 01

    Sorry again for this trouble really appreciated what you guys done so far.
    Last edited by wann; 09-19-2006 at 11:39 PM.

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    From what you say, the number of 'permutations' depends upon the number of different characters (in any order) (but hey, I'm no mathematician)
    and I have noticed that my formula is incomplete.

    If you have 4 different digits then you are 24

    If you are 3 different digits then, (I hope, irrespective of which two match) you are 12

    If you are 1 different digit you are 1

    My formula handles those

    however, I just realised that if you have two different digit you can be 1-3 or 2-2

    2-2 = 6 (as shown by you yesterday)

    1-3 is 4 (not handled by my formula).

    When you have the number of different digits you know all but the 2-2split = 4

    ie, 4 = 24
    3 = 12
    2(3-1) = 6
    2(2-2) = 4
    1 = 1

    I will amend the formula to test for the 2-2 split and that should be whast you require.

    Agree so far?

    ---

    Quote Originally Posted by wann
    Sorry Bryan, I'm still don't get what i'm expected from the formula given...

    Let me explain again in diffrent way...

    Example 1.
    Four different numbers in A1 (1234). In how many different ways could you arrange them?

    My answer in A2 24

    No matter what numbers or any form of numbers arrangement from 0000 to 9999 that I need to enter in A1...

    (1234) = 24 or M24
    (1233) = 12 or M12
    (1212) = 06 or M06
    (1211) = 04 or M04
    (1111) = 01 or M01 or STRAIGHT

    (6789) = 24
    (6988) = 12
    (1010) = 06
    (9000) = 04
    (0000) = 01

    Sorry again for this trouble really appreciated what you guys done so far.
    Last edited by Bryan Hessey; 09-20-2006 at 12:44 AM.

  10. #10
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    Bryan, please see attachment in zip for myproject.xls file. Hope you find it much clearly and understand what I'm doing with it...

    Yes, that's what I've meant.. sorry for so much trouble...

    Many thanks.
    Wann
    Attached Files Attached Files
    Last edited by wann; 09-20-2006 at 02:08 AM.

  11. #11
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Unfortunately not, but I think the first part is solved.

    To get the M number, store the formula:
    =IF((1+IF(MID(A1,3,1)=MID(A1,4,1),0,1)+IF(ISERROR(FIND(MID(A1,2,1),MID(A1,3,2))),1,0)+IF(ISERROR(FIND(LEFT(A1,1),MID(A1,2,3))),1,0))=2,IF(OR((ISERROR(FIND(LEFT(A1,1),MID(A1,2,3)))),ISERROR(FIND(RIGHT(A1,1),MID(A1,1,3)))),31,IF(LEN(SUBSTITUTE(A1,LEFT(A1,1),""))=1,31,22)),1+IF(MID(A1,3,1)=MID(A1,4,1),0,1)+IF(ISERROR(FIND(MID(A1,2,1),MID(A1,3,2))),1,0)+IF(ISERROR(FIND(LEFT(A1,1),MID(A1,2,3))),1,0))

    in a cell (say A2) and from that cell (in say, A3) use the formuls:

    ="M"&IF(A2=4,24,IF(A2=3,18,IF(A2=1,1,IF(A2=31,4,6))))

    to give the answer required for the first cell (I didn't check this formula)

    The second cell is simply

    =Mid(A2,2,2)+0

    formatted to money.

    note, you need to adjust the formula to your cells, in the Forum we use A1 etc to keep the formula format standard.

    hth
    ---

    Quote Originally Posted by wann
    Bryan, please see attachment in zip for myproject.xls file. Hope you find it much clearly and understand what I'm doing with it...

    Yes, that's what I've meant.. sorry for so much trouble...

    Many thanks.
    Wann

  12. #12
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    For this formula, I'm still don't get it... still give me the same result...

    =IF((1+IF(MID(A1,3,1)=MID(A1,4,1),0,1)+IF(ISERROR(FIND(MID(A1,2,1),MID(A1,3,2))),1,0)+IF(ISERROR(FIND(LEFT(A1,1),MID(A1,2,3))),1,0))=2,IF(OR((ISERROR(FIND(LEFT(A1,1),MID(A1,2,3)))),ISERROR(FIND(RIGHT(A1,1),MID(A1,1,3)))),31,IF(LEN(SUBSTITUTE(A1,LEFT(A1,1),""))=1,31,22)),1+IF(MID(A1,3,1)=MID(A1,4,1),0,1)+IF(ISERROR(FIND(MID(A1,2,1),MID(A1,3,2))),1,0)+IF(ISERROR(FIND(LEFT(A1,1),MID(A1,2,3))),1,0))

    in a cell (say A2) and from that cell (in say, A3) use the formuls:

    ="M"&IF(A2=4,24,IF(A2=3,18,IF(A2=1,1,IF(A2=31,4,6))))

    to give the answer required for the first cell (I didn't check this formula)
    and as for this formula thank you...

    =Mid(A2,2,2)+0

    In the lefthand column is entirely 4 digit numbers. They are all combinations of the numbers 0 through 9. I need to group the rows such that all the 4-digit "words" which are made up of the same combination of four numbers - are together. The idea is to eliminate duplicate combinations (i.e. 1256, 1526, 1625, etc.)

    Thanks
    Wann

  13. #13
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Try the attached - put your numbers in A1


    Quote Originally Posted by wann
    For this formula, I'm still don't get it... still give me the same result...

    and as for this formula thank you...

    In the lefthand column is entirely 4 digit numbers. They are all combinations of the numbers 0 through 9. I need to group the rows such that all the 4-digit "words" which are made up of the same combination of four numbers - are together. The idea is to eliminate duplicate combinations (i.e. 1256, 1526, 1625, etc.)

    Thanks
    Wann
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    Quote Originally Posted by Bryan Hessey
    Try the attached - put your numbers in A1
    OK for now Bryan... Thanks! Quite complicated wish the formula much more simpler and short. Thanks again.

    BTW, how can I add a "M06" instead getting "M6" 3 characters as "M24, M12, M06, M04, M01"

    ="M"&IF(A2=4,24,IF(A2=3,18,IF(A2=1,1,IF(A2=31,4,6))))
    Many thanks,
    Wann
    Last edited by wann; 09-20-2006 at 05:47 AM.

  15. #15
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    ="M"&IF(A2=4,24,IF(A2=3,18,IF(A2=1,text(1,"00"),IF(A2=31,text(4,"00"),text(6,"00")) )))

    should do the trick, or you can format the cell as 'Text' and just use "06"

    For the length of the formula, maybe someone can do a shorter version with a real math formula, but that's out of my league, I just do these simple ones.

    Note you can spread the 4 digits over B1, B2, B3 and B4 and do:

    =SUM(IF(FREQUENCY(MATCH(B1:B4,B1:B4,0),MATCH(B1:B4,B1:B4,0))>0,1))

    but this will not take the 2-2 / 3-1 split into account.

    ---

    Quote Originally Posted by wann
    OK for now Bryan... Thanks! Quite complicated wish the formula much more simpler and short. Thanks again.

    BTW, how can I add a "M06" instead getting "M6" 3 characters as "M24, M12, M06, M04, M01"



    Many thanks,
    Wann
    Last edited by Bryan Hessey; 09-20-2006 at 06:08 AM.

  16. #16
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    Quote Originally Posted by Bryan Hessey
    ="M"&IF(A2=4,24,IF(A2=3,18,IF(A2=1,text(1,"00"),IF(A2=31,text(4,"00"),text(6,"00")) )))

    should do the trick, or you can format the cell as 'Text' and just use "06"

    For the length of the formula, maybe someone can do a shorter version with a real math formula, but that's out of my league, I just do these simple ones.

    Note you can spread the 4 digits over B1, B2, B3 and B4 and do:

    =SUM(IF(FREQUENCY(MATCH(B1:B4,B1:B4,0),MATCH(B1:B4,B1:B4,0))>0,1))

    but this will not take the 2-2 / 3-1 split into account.

    ---

    Thanks Bryan. Really appreciated.

    Regards,
    Wann

  17. #17
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    If you sparse out the digits into the 4 cells B1, C1, D1, and E1 in F1 you could have this formula

    =SUM(COUNTIF(B1:E1,B1),COUNTIF(B1:E1,C1),COUNTIF(B1:E1,D1),COUNTIF(B1:E1,E1))

    If the result is 16 then there is 1 permutation
    If the result is 10 then there are 4 permutations
    If the result is 8 then there are 6 permutations
    If the result is 6 then there are 12 permulations
    If the result is 4 then there are 24 permutations

    And you can setup a table and do a quick lookup based on the result

    This only works with 4 digit numbers though.

  18. #18
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    Quote Originally Posted by kraljb
    If you sparse out the digits into the 4 cells B1, C1, D1, and E1 in F1 you could have this formula

    =SUM(COUNTIF(B1:E1,B1),COUNTIF(B1:E1,C1),COUNTIF(B1:E1,D1),COUNTIF(B1:E1,E1))

    If the result is 16 then there is 1 permutation
    If the result is 10 then there are 4 permutations
    If the result is 8 then there are 6 permutations
    If the result is 6 then there are 12 permulations
    If the result is 4 then there are 24 permutations

    And you can setup a table and do a quick lookup based on the result

    This only works with 4 digit numbers though.
    Excellent! Is it possible if you can make the 4 digits into 1 cell instead of 4 cells using this formula? Can you show me how...

    Thank you.

    --
    Wann

  19. #19
    Forum Contributor kraljb's Avatar
    Join Date
    05-26-2004
    Location
    Illinois
    MS-Off Ver
    2007 (recent change)
    Posts
    256
    =SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,1,1)))+SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,2,1)))+SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,3,1)))+SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,4,1)))

    I was using cell I28, but you can use change that to cell A1

    Then throw a lookup on it to find the coorlating permutations with the result as stated before...

    Hope that helps...

  20. #20
    Registered User
    Join Date
    09-16-2006
    Posts
    21
    Quote Originally Posted by kraljb
    =SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,1,1)))+SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,2,1)))+SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,3,1)))+SUMPRODUCT(--(MID(I28,ROW(INDIRECT("1:4")),1)=MID(I28,4,1)))

    I was using cell I28, but you can use change that to cell A1

    Then throw a lookup on it to find the coorlating permutations with the result as stated before...

    Hope that helps...
    Excellent! Thanks to Krajb and Bryan for the great help. Everything is in order now.

    Thanks.

    --
    Wann

  21. #21
    Registered User
    Join Date
    06-04-2007
    Posts
    12

    Compare 2 permutation number

    Dear all expert,

    I need to compare 2 permutation number

    1234 3124 true
    1234 1325 false

    I had an array to compare permutation between A1 and B1:E1
    A1 B1 C1 D1 E1
    1234 2134(true) 3234(false) 4231(true) 3412 (true)

    Please help...thanks

    If possible, i like it to be in excel function instead of VB code. Thanks again.

  22. #22
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Please start a new thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1