Randomizing a Quiz with Excel (or some other program)

[Vinyadan]

I'm trying to create a create an easy way to mix things up with a quiz.

Let's say that I have these questions, each question has 4 answers to choose from:

1. How old are you?

• 15
• 16
• 17
• 18

2. What's your favourite colour?

• Blue
• Green
• Grey
• Dun

3. Do you like cats?

• Yes
• No
• A bit
• Rarely

Now I want to put question 1-3 in a different order, and also to scramble their answers. I can do that with Excel, but I get some problems.

Each column is a question, with its answers.

	random number	random number	random number
	Question	Question	Question
random number	Answer	Answer	Answer
random number	Answer	Answer	Answer

The random numbers are made with RANDBETWEEN. This works very well, however I can't easily print this, because excel tries to print what it sees (four rows with a lot of columns), so it prints the top of a page and leaves the rest empty, essentially wanting to make a collage that preserves the 4 rows-100s columns look. This means hundreds of pages.

Is there a better way to do this, even starting from scratch?

[Brother Oni]

What happens if you highlight the cells you want to print, go to the Page Layout ribbon > Print Area > Set Print Area, then try printing that?

[Vinyadan]

What happens if you highlight the cells you want to print, go to the Page Layout ribbon > Print Area > Set Print Area, then try printing that?

The result is the same, I get the "poster" format with a very long stripe that extends for many pages.

There's another problem, which I didn't specify earlier, and it's that I cannot print cells that contain line breaks right. I get a normal-sized cell centered on the empty room between the two lines, with the actual text only partially visible.

[Khedrac]

I think a good way would be to swap the rows and columns, so you get the questions down as one column with three columns of answers - that would come a lot closer to being a printable layout.

If you really want to customise your print layout you probably need to create a Word Mailmerge document from the Excel file of questions and answers - that way you can sort out the formatting in Word (including stopping individual questions from breaking over pages) and populate it with your randomised Excel list.

[Vinyadan]

I have tried the swap, but the questions and the answers can be very, very long and I get the same problem.

How do I use mailings? I've watched a couple videos, but I've never had a mailings button, as far as I can remember.

ATM I am using a very rough workaround by printing the excel as PDF, using one website to remove the white from the pages, another website to turn the pages into images, then another website to merge the images into one image, and finally using Paint to print the image breaking it over multiple pages. I still have the problem with cells containing more than one line of text, however.

[jayem]

The result is the same, I get the "poster" format with a very long stripe that extends for many pages.

so long as you drag all the relevant aspects, you could drag the presentations rows to where you want them (or cut and paste, but not copy and paste). That would at least be a one off cost,
That is having got cells A1:Z1 containing each question, you drag cells E1:Z1 to cells A10:V10
You can then play with row-height so the biggest question fits nicely in a row (check the text is wrapped and possibly aligned top)
...


I don't know if there's a simple way to shuffle*, Randbetween ought to lead to duplication if you don't hae a significantly larger question bank than the posed questions.
*If you get desperate and there's a prime number of things, modular addition ought to give a permutation of question order, it won't be random random.

[Kato]

I don't know about excel but depending on what kind of output you want I'm sure there are plenty of programs to do this..
If you use something math oriented like octave or math lab or whatever create a string array with the questions and answers.
Then make a pair of nested loops, the first rolling a number for the question and inside that a number for the answers. You just need a bit of code to prevent duplicates I can't think of off the top of my head but it shouldnt be too hard. Or, I think, something similar should be possible in html if you prefer that.

[Algeh]

How do I use mailings? I've watched a couple videos, but I've never had a mailings button, as far as I can remember.

Mailings would be a tab in Word that you'd use to pull in fields from your Excel spreadsheet into a Word document (there are also other programs that can do a mail merge, but Word is the one you're most likely to have and know how to use if you're already using Excel).

For example, I use a mail merge to print each student's name on their test. The Excel file contains a column titled "name" with a student name in each row, and I then import that field into Word so I can print out a class set of tests with student names already on them, along with some other fields stored in other columns that helps me keep things organized.

Which version of Word are you using?