Calculate how many PERMUTATION possible

[Sh3nmue]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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]

I have another example.. I found it from book...

but that's just works for all letters from the word MISSISSIPPI ...(arrange 11 from 11)
but how if I want to arrange just 3 letters from the word MISSISPPI...(arrange 3 from 11)

I count it using permutation generator online from JavaScript Permutations Generator
and it shows me 53 possible permutation of 3 letters from the word MISSISSIPPI

I just want to find out the formula so I can work with excel...

sorry for my bad English..

this is the result from permutation generator web:

 

[StephenCrump]

The 11 out of 11 case is relatively simple because it uses all the letters. There are 11! ways of re-arranging, including duplicates. With 4 i's always in the rearranged word, there are 4! ways of arranging i1, i2, i3 and i4, hence you need to divide by 4! to eliminate the i duplicates. And similar for any other duplicated letter.

Unfortunately there's no single simple formula when you are selecting less than the full number of letters.

If you want to count the number of permutations if you select three letters, say, you can do it two ways:

1. Use an algorithm like the VBA provided previously. This will list all 11x10x9 = 990 permutations, including duplicates. You can then use Data/Remove Duplicates to generate a list of non-duplicates, and count these.

2. You can use a formula approach, but as I said in #7 above, it gets tricky listing all the duplicate possibilities to be eliminated. In this case, these are:

i. 3 x i's selected. This can happen in 4x3x2=24 ways. Hence we need to eliminate 23 from the count.

ii. 2 i's and 1 s. There are 3 ways this can happen: iis, isi or sii. For each of these 3, there are 4x3=12 ways of selecting the i's and 4 ways of selecting the s, i.e. 48 possibilities. So we need to eliminate 3 x 47 = 141 from the count.

...

You'll need to consider many other combinations of duplicates, including;
2 x i, 1 x p
2 x i, 1 x X (where X is not i,s or p)
1 x i , 1 x s, 1 x p
3 x s
2 x s, 1 x p
2 x s, 1 x X etc etc.

There will be some symmetries given that there are 4 i's and 4 s's, e.g. the number of permutations with 2 x s, 1 x X will be the same as for 2 x i, 1 x X.

So this approach is time consuming, and tricky ... you have to think like a computer! I don't guarantee that my numbers above are correct, but the process is.

 

[StephenCrump]

Duhh! I really should think before I post ....

Much simpler if we come at it from the other direction:

1. There are 4! = 24 ways of permuting MISP

2. There are three ways we can have a double letter, i.e. S, I or P. For double P, we can have PPX, PXP or XPP, and there are 3 ways of choosing X, therefore 3x 3 x 3 = 27 ways of having a double letter.

3. There are two ways of having a triple, i.e. III or SSS.

24 + 27 + 2 = 53!

 

[Sh3nmue]

Duhh! I really should think before I post ....

Much simpler if we come at it from the other direction:

1. There are 4! = 24 ways of permuting MISP

2. There are three ways we can have a double letter, i.e. S, I or P. For double P, we can have PPX, PXP or XPP, and there are 3 ways of choosing X, therefore 3x 3 x 3 = 27 ways of having a double letter.

3. There are two ways of having a triple, i.e. III or SSS.

24 + 27 + 2 = 53!

sorry, it seems I do not understand u,,, ^^ hahaha...
u said that There are 4! = 24 ways of permuting MISP(it's 4 letters without duplicate), but how if i want 5 permutation?
can u explain it maybe with another example... or write down the formula?
n how to work it with excel...?
I guess it should use VBA instead of usual formulas in excel,,

 

[Sh3nmue]

Bump

 

[StephenCrump]

In summary, all I'm saying is that once you get beyond the really simple questions, such as how many permutations of 3 can I make from 5 different letters, there is no simple, single formula that you can use manually, or codify into Excel.

Instead, you have to start thinking about the possible sub-sets, and determine the numbers and/or probabilities for each.

For example, if we want the number of 5-letter permutations you can make from MISSISSIPPI, you can add:

1. Number of permutations with no duplicates = NIL obviously, plus

2. Number of permutations with exactly one duplicate:
- Possible duplicate letters = I,S or P = 3
- No of different duplicates for each letter e.g. IIXXX, IXIXX, ... XXXII = 10 =COMBIN(5,2)
- No of ways of selecting remaining three X = 3! = 6 = PERMUT(3,3)
Subtotal = 3 x 10 x 6= 180

3. Number of permutations with exactly two duplicates:
- Number of ways of selecting two duplicates, (i.e. out of I,S and P) = 3 = COMBIN(3,2)
- Number of different duplicates for each letter = COMBIN(5,2) x COMBIN(3,2) = 30, i.e. 10 ways of placing the first duplicate, and for each of these, 3 ways of placing the second duplicate.
- No of ways of selecting the remaining letter: 2
Subtotal = 3 x 30 x 2 = 180

4. Number of permutations with exactly one triplicate:
- Number of ways of selecting triplicate = 2 (i.e. I or S) = COMBIN(2,1)
- Number of different triplicates for each letter = COMBIN(5,3) = 10
- Number of ways of selecting remaining two letters = PERMUT(3,2) = 6
Subtotal = 2 x 10 x 6 = 120

5. Number of permutations with one double, one triple:
- Number of ways of selecting double, triple = 4, i.e. SSSII, SSSPP, IIISS or IIIPP (=COMBIN(2,1)x COMBIN(2,2))
- Number of different arrangements for each possibility = 10 (=COMBIN(5,3) x COMBIN(2,2))
Subtotal = 4 x 10 = 40

6. Number of permutations with exactly one quadruplicate:
- Number of ways of selecting quadruplicate = 2 (i.e. I or S) = COMBIN(2,1)
- Number of different quadruplicates for each letter = COMBIN(5,4) = 5
- Number of ways of selecting remaining one letter = PERMUT(3,1) = 3
Subtotal = 2 x 5 x 3 = 30

Total = 180 + 180 + 120 + 40 + 30 = 550 (I checked this total using your Permutations Generator).

Before computers, that's the only way you could determine these sorts of numbers/probabilities.

The speed of computers allows you to use brute-force methods, such as that outlined in #12 above.

 

[Sh3nmue]

In summary, all I'm saying is that once you get beyond the really simple questions, such as how many permutations of 3 can I make from 5 different letters, there is no simple, single formula that you can use manually, or codify into Excel.

Instead, you have to start thinking about the possible sub-sets, and determine the numbers and/or probabilities for each.

For example, if we want the number of 5-letter permutations you can make from MISSISSIPPI, you can add:

1. Number of permutations with no duplicates = NIL obviously, plus

2. Number of permutations with exactly one duplicate:
- Possible duplicate letters = I,S or P = 3
- No of different duplicates for each letter e.g. IIXXX, IXIXX, ... XXXII = 10 =COMBIN(5,2)
- No of ways of selecting remaining three X = 3! = 6 = PERMUT(3,3)
Subtotal = 3 x 10 x 6= 180

3. Number of permutations with exactly two duplicates:
- Number of ways of selecting two duplicates, (i.e. out of I,S and P) = 3 = COMBIN(3,2)
- Number of different duplicates for each letter = COMBIN(5,2) x COMBIN(3,2) = 30, i.e. 10 ways of placing the first duplicate, and for each of these, 3 ways of placing the second duplicate.
- No of ways of selecting the remaining letter: 2
Subtotal = 3 x 30 x 2 = 180

4. Number of permutations with exactly one triplicate:
- Number of ways of selecting triplicate = 2 (i.e. I or S) = COMBIN(2,1)
- Number of different triplicates for each letter = COMBIN(5,3) = 10
- Number of ways of selecting remaining two letters = PERMUT(3,2) = 6
Subtotal = 2 x 10 x 6 = 120

5. Number of permutations with one double, one triple:
- Number of ways of selecting double, triple = 4, i.e. SSSII, SSSPP, IIISS or IIIPP (=COMBIN(2,1)x COMBIN(2,2))
- Number of different arrangements for each possibility = 10 (=COMBIN(5,3) x COMBIN(2,2))
Subtotal = 4 x 10 = 40

6. Number of permutations with exactly one quadruplicate:
- Number of ways of selecting quadruplicate = 2 (i.e. I or S) = COMBIN(2,1)
- Number of different quadruplicates for each letter = COMBIN(5,4) = 5
- Number of ways of selecting remaining one letter = PERMUT(3,1) = 3
Subtotal = 2 x 5 x 3 = 30

Total = 180 + 180 + 120 + 40 + 30 = 550 (I checked this total using your Permutations Generator).

Before computers, that's the only way you could determine these sorts of numbers/probabilities.

The speed of computers allows you to use brute-force methods, such as that outlined in #12 above.

Oooow...
so it's not just using permutation but it also using combination??
OMG I thought it will be done with just a simple formula hahaha...
Ok I will study your answer first...
btw thx alot Stephen..

 

[Sh3nmue]

Stephen had a good answer, but I want to find a simple way that can be done in excel...

does anybody have other solutions?

 

[Sh3nmue]

Bump

 

[Sh3nmue]

maybe this case is too difficult...

instead of using letters, how about if we use numbers...

......
I want to calculate for how many permutation possible for number in cell "A"..

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

	A	B	C	D	E	F
1	Numbers	Permute 6	Permute 5	permute 4	Permute 3	Permute 2
2	1234			24	24	12
3	1123			12	12	7
4	3334			4	4	3
5	5656			6	6	4
6	12345		120	120	60	20
7	11234			60	33	13
8	11223			30	18	8
9	123456	720	720	360	120	30
10	112345
11	111234
12	112234

<tbody>
</tbody>

Sheet1​

you can use this (JavaScript Permutations Generator) to calculate it if u want to know the result, but how to do it in excel..
I don't want to generate the list of permutation, but I just want to know how many permutation can be made.