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
1	A	B	C	D	E	F	G	H
2	1	1	1	1	1	1	1	1
3	1	1	1	1	1	1	1	2
4	1	1	1	1	1	1	1	3
5	1	1	1	1	1	1	1	4
6	1	1	1	1	1	1	1	5
7	1	1	1	1	1	1	2	1
8	1	1	1	1	1	1	2	2
9	1	1	1	1	1	1	2	3
10	1	1	1	1	1	1	2	4
11	1	1	1	1	1	1	2	5
12	1	1	1	1	1	1	3	1
13	1	1	1	1	1	1	3	2
14	etc
15	5	5	5	5	5	5	5	5

<tbody>
</tbody>

Sheet1​

 

[froggeja]

Hi Stephen,

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

Question	1	2	3	4	5
A	A1	A2	A3	A4	A5
B	B1	B2	B3	B4	B5
C	C1	C2	C3	C4	C5
D	D1	D2	D3	D4	D5
E	E1	E2	E3	E4	E5
F	F1	F2	F3	F4	F5
G	G1	G2	G3	G4	G5
H	H1	H2	H3	H4	H5

<tbody>
</tbody>

I now want to be able to populate this grid:

Permutation	A	B	C	D	E	F	G	H
PERM 1	A1	B1	C1	D1	E1	F1	G1	H1
PERM 2	A1	B2	C1	D1	E1	F1	G1	H1
PERM 3	A1	B3	C1	D1	E1	F1	G1	H1
ETC,.

<tbody>
</tbody>

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
1	Permutation	A	B	C	D	E	F	G	H
2	1	A1	B1	C1	D1	E1	F1	G1	H1
3	2	A1	B1	C1	D1	E1	F1	G1	H2
4	3	A1	B1	C1	D1	E1	F1	G1	H3
5	4	A1	B1	C1	D1	E1	F1	G1	H4
6	5	A1	B1	C1	D1	E1	F1	G1	H5
7	6	A1	B1	C1	D1	E1	F1	G2	H1
8	7	A1	B1	C1	D1	E1	F1	G2	H2
9	8	A1	B1	C1	D1	E1	F1	G2	H3
10	9	A1	B1	C1	D1	E1	F1	G2	H4
11	10	A1	B1	C1	D1	E1	F1	G2	H5
12	11	A1	B1	C1	D1	E1	F1	G3	H1
13	12	A1	B1	C1	D1	E1	F1	G3	H2
14	etc
15	390,622	A5	B5	C5	D5	E5	F5	G5	H2
16	390,623	A5	B5	C5	D5	E5	F5	G5	H3
17	390,624	A5	B5	C5	D5	E5	F5	G5	H4
18	390,625	A5	B5	C5	D5	E5	F5	G5	H5

<tbody>
</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.