Calculate how many PERMUTATION possible

Sh3nmue · Jan 20, 2014

Hello Me Excel... I really need help again here...
I want to calculate for how many permutation possible for number in cell..

just for example:
in collumn A have list of number or letters
column B is permut by 6
column C is permut by 5 and etc...

	A	B	C	D	E	F
1	Number/Letters	Permute 6	Permute 5	permute 4	Permute 3	Permute 2
2	abcd			24	24	12
3	aabc			12	12	7
4	aaab			4	4	3
5	aabb			6	6	4
6	abcde		120	120	60	20
7	aabcd			60	33	13
8	aabbc			30	18	8
9	abcdef	720	720	360	120	30
10	aabcde
11	aaabcd
12	aabbcd

Sheet1

all the value in the table above is calculated manually and it wasting my time an paper,, LoL,,

because i have to write down all possible permutation and count it...
can someone help me with this...
Thank You

Sh3nmue · Jan 20, 2014

this is how i count it manually...

example:

aabc
permute 4:
aabc.aacb.abac.abca.acab.acba.baac.baca.bcaa.caab.caba.cbaa. =12

permute 3:
aab.aac.aba.abc.aca.acb.baa.bac.bca.caa.cab.cba. =12

permute 2:
aa.ab.ac.ba.bc.ca.cb. =7

....

aabb
permute 4:
aabb.abab.abba.baab.baba.bbaa. =6

permute 3:
aab.aba.abb.baa.bab.bba. =6

permute 2:
aa.ab.ba.bb. =4

....

aabcd
permute 5:
aabcd.aabdc.aacbd.aacdb.aadbc.aadcb.abacd.abadc.abcad.abcda.abdac.abdca.acabd.acadb.acbad.
acbda.acdab.acdba.adabc.adacb.adbac.adbca.adcab.adcba.baacd.baadc.bacad.bacda.badac.badca.
bcaad.bcada.bcdaa.bdaac.bdaca.bdcaa.caabd.caadb.cabad.cabda.cadab.cadba.cbaad.cbada.cbdaa.
cdaab.cdaba.cdbaa.daabc.daacb.dabac.dabca.dacab.dacba.dbaac.dbaca.dbcaa.dcaab.dcaba.dcbaa. =60

permute 4:
aabc.aabd.aacb.aacd.aadb.aadc.abac.abad.abca.abcd.abda.abdc.acab.acad.acba.
acbd.acda.acdb.adab.adac.adba.adbc.adca.adcb.baac.baad.baca.bacd.bada.badc.
bcaa.bcad.bcda.bdaa.bdac.bdca.caab.caad.caba.cabd.cada.cadb.cbaa.cbad.cbda.
cdaa.cdab.cdba.daab.daac.daba.dabc.daca.dacb.dbaa.dbac.dbca.dcaa.dcab.dcba. =60

permute 3:
aab.aac.aad.aba.abc.abd.aca.acb.acd.ada.adb.adc.baa.bac.bad.bca.
bcd.bda.bdc.caa.cab.cad.cba.cbd.cda.cdb.daa.dab.dac.dba.dbc.dca.dcb. =33

permute 2:
aa.ab.ac.ad.ba.bc.bd.ca.cb.cd.da.db.dc. =13

its really take time...

StephenCrump · Jan 20, 2014

You are going to kick yourself! (I hope this translates OK for any non-English natives) ...

Check out Excel's PERMUT and COMBIN functions.

Edit:

Ooops, sorry, I posted too quickly. Didn't read the question and see you had repeating letters. That complicates things: You can still use a formula approach but adjusted for numbers of repeats. I'll update later unless someone else gives you a proper answer in the meantime.

Sh3nmue · Jan 20, 2014

StephenCrump said:
You are going to kick yourself! (I hope this translates OK for any non-English natives) ...

Check out Excel's PERMUT and COMBIN functions.

Edit:

Ooops, sorry, I posted too quickly. Didn't read the question and see you had repeating letters. That complicates things: You can still use a formula approach but adjusted for numbers of repeats. I'll update later unless someone else gives you a proper answer in the meantime.

hello...haha...
I already try it with PERMUT function,, but it does not work with reapeting numbers or letters...
btw thx for replying

AhoyNC · Jan 20, 2014

If you real want the permutation repeating numbers or letters still have to be counted.
Example;
Take your aabc
3 permutation

You have 2 different a's (lets say a1 & a2)

So aab could be (a1 a2 b ) or (a2 a1 b) so instead of 12 you actual have 24 permutations.

Drrellik · Jan 20, 2014

AhoyNC is right if you are looking at Aabc using 3 so that Aab and aAb are counted then the =permut(4,3) returns 24 which is correct.

StephenCrump · Jan 20, 2014

As I said previously, it gets complicated because you have to start counting the various possibilities ...

For example, with abcde, there are 60 possible permutation of 3.

Similarly, with aabcd, there are 60 possible permutations (including duplicates).

These duplicates consist of:

a) 2 occurrences of A, i.e.

AAX
AXA
XAA

There are 3 ways this can occur, and X can take 3 values (i.e. b, c or d) so this is 9 possibilities.

b) One occurrence of A, i.e.

AXX
XAX
XXA

Each occurrence can be filled 3 x 2 ways, i.e. first X can be b, c or d, and 2nd X can be from remaining two letters,
so this is 3x3x2 = 18 possibilities.

60 - 9 - 18 = 33 = those possibilities you have listed manually.

You can ramp this up for multiple duplicates, e.g. using something like aaabcdeeeeff, but you'll have to think really carefully to count all the possibilities for duplicates.

Jim885 · Jan 20, 2014

Perhaps something like this will help. This code will produce all 720 permutations for values of 1,2,3,4,5, and 6. (a six figure value)
You can play with the code for different situations.
I did not write it. I think hiker95 did. He is a genius.

Code:

Sub Permutations()
lRow = 2
For a = 1 To 6
  For b = 1 To 6
    If b <> a Then
      For c = 1 To 6
        If c <> a And c <> b Then
          For d = 1 To 6
            If d <> a And d <> b And d <> c Then
              For e = 1 To 6
                If e <> a And e <> b And e <> c And e <> d Then
                  For f = 1 To 6
                    If f <> a And f <> b And f <> c And f <> d And f <> e Then
                      Range("A" & lRow).Value = a
                      Range("B" & lRow).Value = b
                      Range("C" & lRow).Value = c
                      Range("D" & lRow).Value = d
                      Range("E" & lRow).Value = e
                      Range("F" & lRow).Value = f
                      lRow = lRow + 1
                    End If
                  Next f
                End If
              Next e
            End If
          Next d
        End If
      Next c
    End If
  Next b
Next a
MsgBox "Done"
End Sub

Jim885 · Jan 20, 2014

You might also be able to use this website; Combinations and Permutations Calculator

Sh3nmue · Jan 21, 2014

AhoyNC said:
If you real want the permutation repeating numbers or letters still have to be counted.
Example;
Take your aabc
3 permutation

You have 2 different a's (lets say a1 & a2)

So aab could be (a1 a2 b ) or (a2 a1 b) so instead of 12 you actual have 24 permutations.

Drrellik said:
AhoyNC is right if you are looking at Aabc using 3 so that Aab and aAb are counted then the =permut(4,3) returns 24 which is correct.

I'm sorry maybe I'm confusing u all...
this is what I want...

Aab n aAb is count as 1, is the same
(a1 a2 b) is same as (a2 a1 b)

Calculate how many PERMUTATION possible

Sh3nmue

Board Regular

Excel Facts

Sh3nmue

Board Regular

StephenCrump

MrExcel MVP

Sh3nmue

Board Regular

AhoyNC

Well-known Member

Drrellik

Well-known Member

StephenCrump

MrExcel MVP

Jim885

Well-known Member

Jim885

Well-known Member

Sh3nmue

Board Regular

Similar threads

Forum statistics

Share this page

Calculate how many PERMUTATION possible

Board Regular

Excel Facts

Board Regular

MrExcel MVP

Board Regular

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock