Printing permutations in Excel

  • Thread starter Thread starter sigmma
  • Start date Start date
S

sigmma

Admin please delete my first 2 posts....Dont know what happened!!!!


Hi Could anyone please help me????

I would like excel to work out and print the permutations of 10 thing
taken 4 times at a time.

ie. Objets are the numbers 1,2,3,4,5,6,7,8,9,0 and I want to generat
sets of 4 numbers, like (1,2,3,4) (3,5,6,7) and so on. I know tha
there are 5040 permutations, but I would like to print them out....i
this possible????

I would appreciate any suggestions........


Thanks in advance
 
Sigmma,

At first : If you (as I understand from your question) don't want to repeat
the figures in one set (i.e. sets as 1223 aren't allowed), the number of
permutations isn't 5040 but 210 ( 10! / ( 4! * 6!) or FACT(10) / (
FACT(4) * FACT(6) )

Second : I think that what you want can't be done with "normal"
Excelfunctions. You would have to write a VBA routine to perform that.
Probably the effort you put in that isn't worth it, it would be faster to do
it by hand.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi.... sorry maybe i didn't explain myself properly....

Numbers can be repeated, so 3333, 4444, are acceptable.

I think the equation goes like this: P= n!/ (n-k)!....where n=10 items
(1,2,3,4,5,6,7,8,9,0) and k= sets of 4 numbers = 5040...

Thats why I dont want to do it manually.........
 
Hi Sigmma,

I think you are still wrong.

What you now describe gives 10^4 results ( 4 positions ; each position can
be 0, 1, 2, 3 ... , 9)
Total number of results thus : 10000.

What your formula implies ( P = n! / (n-k)! ) is known as variations which
doesn't allow 1334 (i.e. double figures); but does allow 1234 and 4321 and
1324 (same figures in various permutations).

Below are two macro's which give the first (10000) combinations ( Sub
Permutations) and the second ( 5040) variations ( Sub Variations).

Copy these routines in a "normal" module and format column A and B with
custom format 0000.
Permutations writes its results in column A , Variations in column B.

I hope that either one of the two is what you want.

Sub Permutations()
RowNr = 1
For I = 0 To 9
For J = 0 To 9
For K = 0 To 9
For L = 0 To 9
Cells(RowNr, 1) = I * 1000 + J * 100 + K * 10 + L
RowNr = RowNr + 1
Next L
Next K
Next J
Next I
End Sub

Sub Variations()
RowNr = 1
Ilow = 0
For I = 0 To 9
For J = 0 To 9
If J <> I Then
For K = 0 To 9
If K <> J And K <> I Then
For L = 0 To 9
If L <> K And L <> J And L <> I Then
Cells(RowNr, 2) = I * 1000 + J * 100 + K * 10 + L
RowNr = RowNr + 1
End If
Next L
End If
Next K
End If
Next J
Next I
End Sub

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Thanks...I think I need to go back to high school and learn a bit more
about Permutations in general.....thanks for routines I'll give them
ago......and report back
 
I would like excel to work out and print the permutations of 10 things
taken 4 times at a time.
Click to expand...


I believe your original Permutation was correct.

=PERMUT(10,4)

returns:
5040
 
Dana, Sigmma,

Excel does indeed give PERMUT(10,4) = FACT(10) / FACT(6).
In the first posts I did not recognize this.

Permutations (of a group of k *different* elements) are however (AFAIK) the
number of ways these elements can be arranged without any element being
repeated in any of these sorts.
Example : a, b, c thus k = 3
abc, acb, bac, bca, cab, cba are the six different sorts and thus P(3) = 6
{ P3 = 6! = Fact(6) }

Variations ( from a group of k elements out of a group of n *different*
elements) are (again AFAIK) the number of ways groups of k elements can be
taken out of the group of n elements and be permutated.
Example : n elements (a, b, c, d) are grouped and permutated in groups of 2
..
ab, ac, ad, ba, bc, bd, ca, cb, cd, da, db, dc are the twelve different
(permutated) groups :

2
V = n! / (n-k)! = 4! / 2! = 12 (It is this what Excel call
Permutations : Permut(n,k) )
4


Finally : Combinations are the number of Variations, whereby however the
various groups aren't permutated.
So : ab and ba aren't different. Both form the same combination.
Example : Same n elements (a,b,c,d) in groups of 2.
ab, ac, ad, ba, bc, bd, cd, are the six possible combinations :
2
C = n! / ( k! * (n-k)! ) = 4! / (2! * 2!) = 6 ( Excel : Combin(n,k)
4

So the first misunderstanding between Sigmma and me arose when I didn't
recognize the way Excel called Permutations what I know as Variations.
I therefore gave Sigmma the (in my opinion correct) formula of Permutations
and in defining them arose the second misunderstanding about the repetition
of the figures ( elements) which isn't allowed in both Permutations and
Variations.
( Better : They are allowed, but then the names and the formula's should be
different)


Conclusive :
1) I should have been aware of the way Excel calls Permutations what I know
to be Variations
( Not sure but this is maybe a difference in English and Dutch
nomenclature ?? )
2) Sigmma anyhow now has the routine ( which I have named Sub Variations )
that creates the Variations / Permutations.


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
I believe 210 is from a "Combination."

=COMBIN(10,4)
210

On a smaller scale using just {1,2,3,4}:
=COMBIN(4,2) returns 6
The actual combinations would be:

{1, 2}, {1, 3}, {1, 4}, {2, 3}, {2, 4}, {3, 4}

Notice for example, that {1,2} is there, but not {2,1}.
Permutations allow both {1,2}, and {2,1} and that's why the count is larger.

If I understand your updated example, you want to display permutations like
0000,0001,0002...5667,5668... up thru 9999.
Well, this is nothing more than our number system. Pick any number between
0 and 9999 and you should have what you need. Just put the numbers is a
Cell, and perhaps format as "0000" to display any leading zeros. Perhaps
put 0 in A1, 1 in A2, and drag down to A9999. Better to put 1 in A1, and
then use Edit-Fill-Series with a step of 1, and end value of 9999.
Hope I understood the question though.
 
Hi Dana,

You are right. Taking variations 4 elements out of a group of 10 elements
( 0, 1, 2, .... 9), whereby you also allow elements to repeat actually comes
down to our number system (for the numbers 0000 up to and inclusiv 9999).
I however was more "busy" with showing the OP that repeating figures wasn't
what he was (originally) asking for AND that it wasn't confirming with his
formula.

I agree (after your mentioning it) that the program (Sub Permutations) is a
rather complex way of doing something that can be done much more easily in
the way you describe. I didn't however realise it in the time I was
programming it (which was a nice exercise anaway).
(Besides : why do something easy if it can be done more difficult <g> ).

As far as your remark about combination is concerned : Have a look at my
reply to your memo earlier this day (or maybe yesterday for you ?).
Indeed 210 is the number of combinations ( groups of 4 elements out of 10
elements , whereby permutations of the same 4 elements are nor allowed).

Thank you for your remarks on my memo's. I hope to "meet you" here more
often.
Further I do hope I haven't confused the OP too much. He however *does*
have the program (sub Variations) he was looking for.

--
Best Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

Dana DeLouis said:
I believe 210 is from a "Combination."

=COMBIN(10,4)
210

On a smaller scale using just {1,2,3,4}:
=COMBIN(4,2) returns 6
The actual combinations would be:

{1, 2}, {1, 3}, {1, 4}, {2, 3}, {2, 4}, {3, 4}

Notice for example, that {1,2} is there, but not {2,1}.
Permutations allow both {1,2}, and {2,1} and that's why the count is larger.

If I understand your updated example, you want to display permutations like
0000,0001,0002...5667,5668... up thru 9999.
Well, this is nothing more than our number system. Pick any number between
0 and 9999 and you should have what you need. Just put the numbers is a
Cell, and perhaps format as "0000" to display any leading zeros. Perhaps
put 0 in A1, 1 in A2, and drag down to A9999. Better to put 1 in A1, and
then use Edit-Fill-Series with a step of 1, and end value of 9999.
Hope I understood the question though.
Click to expand...
 
Back
Top