Question Permutations

[froggeja]

I have 8 questions (A - H) and 5 answers per question (1 - 5) - so a total of 40 possible answers.

Using PERMUT(8,5), I know there are 6,720 permutations of answers.

I now need to fill a matrix of 840 rows and 8 columns (Question ID) with the 6,720 permutations.

I want to fill them with an Answer ID (there are 40 answers) - A1, A2, A3, A4, A5....B1, B2, B3, B4, B5....and so on,.

Does anyone have a solution to this? Ideally, I would like a formula as opposed to VBA as I'm a little out of my depth when it comes to VBA code. However, I'll take whatever I can get!

Thanks in advance.

 

[StephenCrump]

I don't follow your maths. Do you mean permutations like this, in which case you'll have 5^8 = 390,625 possibilities (rows)?

Excel 2010

	A	B	C	D	E	F	G	H
etc

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]

</tbody>

Sheet1​

 

[froggeja]

Hi Stephen,

This is the data (below). I used the formula PERMUT(8,5) to calculate the permutations: 6,720.

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Question
[/TD]
[TD]1
[/TD]
[TD]2[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]A3
[/TD]
[TD]A4
[/TD]
[TD]A5
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]B1
[/TD]
[TD]B2
[/TD]
[TD]B3
[/TD]
[TD]B4
[/TD]
[TD]B5
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]C1
[/TD]
[TD]C2
[/TD]
[TD]C3
[/TD]
[TD]C4
[/TD]
[TD]C5
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]D1
[/TD]
[TD]D2
[/TD]
[TD]D3
[/TD]
[TD]D4
[/TD]
[TD]D5
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]E1
[/TD]
[TD]E2
[/TD]
[TD]E3
[/TD]
[TD]E4
[/TD]
[TD]E5
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]F1
[/TD]
[TD]F2
[/TD]
[TD]F3
[/TD]
[TD]F4
[/TD]
[TD]F5
[/TD]
[/TR]
[TR]
[TD]G
[/TD]
[TD]G1
[/TD]
[TD]G2
[/TD]
[TD]G3
[/TD]
[TD]G4
[/TD]
[TD]G5
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]H1
[/TD]
[TD]H2
[/TD]
[TD]H3
[/TD]
[TD]H4
[/TD]
[TD]H5
[/TD]
[/TR]
</tbody>[/TABLE]














I now want to be able to populate this grid:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Permutation
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]PERM 1
[/TD]
[TD]A1
[/TD]
[TD]B1
[/TD]
[TD]C1
[/TD]
[TD]D1
[/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[TD]G1
[/TD]
[TD]H1
[/TD]
[/TR]
[TR]
[TD]PERM 2
[/TD]
[TD]A1
[/TD]
[TD]B2
[/TD]
[TD]C1
[/TD]
[TD]D1
[/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[TD]G1
[/TD]
[TD]H1
[/TD]
[/TR]
[TR]
[TD]PERM 3
[/TD]
[TD]A1
[/TD]
[TD]B3
[/TD]
[TD]C1
[/TD]
[TD]D1
[/TD]
[TD]E1
[/TD]
[TD]F1
[/TD]
[TD]G1
[/TD]
[TD]H1
[/TD]
[/TR]
[TR]
[TD]ETC,.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps.

thanks

 

[StephenCrump]

Perhaps like this:

N: =5
B2: =B$1&MOD(INT((ROW()-ROW($B$2))/N^(COLUMN($I$1)-COLUMN())),N)+1 (copy down and across)

Excel 2010

	A	B	C	D	E	F	G	H	I
Permutation
etc

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"]H1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"]H2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"]H3[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"]H4[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"]H5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G2[/TD]
[TD="align: center"]H1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G2[/TD]
[TD="align: center"]H2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G2[/TD]
[TD="align: center"]H3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G2[/TD]
[TD="align: center"]H4[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G2[/TD]
[TD="align: center"]H5[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G3[/TD]
[TD="align: center"]H1[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]D1[/TD]
[TD="align: center"]E1[/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"]G3[/TD]
[TD="align: center"]H2[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]390,622[/TD]
[TD="align: center"]A5[/TD]
[TD="align: center"]B5[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"]D5[/TD]
[TD="align: center"]E5[/TD]
[TD="align: center"]F5[/TD]
[TD="align: center"]G5[/TD]
[TD="align: center"]H2[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]390,623[/TD]
[TD="align: center"]A5[/TD]
[TD="align: center"]B5[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"]D5[/TD]
[TD="align: center"]E5[/TD]
[TD="align: center"]F5[/TD]
[TD="align: center"]G5[/TD]
[TD="align: center"]H3[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]390,624[/TD]
[TD="align: center"]A5[/TD]
[TD="align: center"]B5[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"]D5[/TD]
[TD="align: center"]E5[/TD]
[TD="align: center"]F5[/TD]
[TD="align: center"]G5[/TD]
[TD="align: center"]H4[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]390,625[/TD]
[TD="align: center"]A5[/TD]
[TD="align: center"]B5[/TD]
[TD="align: center"]C5[/TD]
[TD="align: center"]D5[/TD]
[TD="align: center"]E5[/TD]
[TD="align: center"]F5[/TD]
[TD="align: center"]G5[/TD]
[TD="align: center"]H5[/TD]

</tbody>

Sheet1​

PERMUT(8,5) is the answer to a different question, e.g. We have covered 8 topics this semester. The exam paper will include 5 of these topics (randomly selected). How many possible exam papers, i.e. combinations of topics, can there be? Answer: COMBIN(8,5) = 56

How many different orders of examination paper topics can there be? Answer: PERMUT(8,5) = 6,720. Because for any of the possible subject combinations in COMBIN(8,5), e.g. A, C, D, F, G (say), we can arrange these subjects in 5! ways, i.e. five possible subjects for the first question, 4 for the second etc etc, and 56 x 5! = 6,720.

 

[froggeja]

Thanks Stephen - that was really helpful. Much appreciated.

 

[StephenCrump]

Great! You're welcome.