random question generator

[southernsquid]

I'm sure this has been addressed before, and I'm sure one of you 50lb heads has the answer I'm looking for. I want to generate a bank of 200-300 questions in excel and be able to make a test of 50 questions that will be different every time. I want to be able to do this with multiple choice questions as well as just plain essay questions. Can I have some instruction or examples? Thanks guys!

 

[Jello]

maybe not I get lots of duplicates. how would the formula be using MRAND function?

 

[Mackers]

maybe not I get lots of duplicates. how would the formula be using MRAND function?

Hi Jello

One possible solution: next to your questions, add a helper column that contains =RAND(). Let's say your questions are in A2:A500 and your RAND() function is in B2:B500. Now, in your 50 cells, put the formula:

=INDEX($A$2:$A$500, RANK($B2, $B$2:$B$500))

Hope that helps

Mackers

 

[Jello]

Thanks Mackers that seems to have fixed the duplicate issue, now how to figure out the highlighted text not transferring over?

this is a sample of the issue:

When a small diameter tube is placed in a glass of water, the water rises in the tube to a level above the adjacent surface. This is called?

a) Viscosity
b) Capillary action
c) Surface tension
d) Barometric testing

the text is highlighted for grading the exam, without it highlighted the examiner would have to search thru the entire list of questions to find the answer

 

[Mackers]

I don't think you can pull through formatting like that with a formula, or at least I don't know how. You could use VBA to do it but that's beyond me.

My suggestion would be to instead add a symbol (maybe an asterisk *) next to the correct answer, so you can see which one it is without formatting.

 

[Jello]

ok thanks Mackers, I cant really use a symbol as it would give the answer away to the employee, I need a way to highlight the correct answer for the examiner only even if it is only on the pc. I'll keep searching lol

 

[Mackers]

ok thanks Mackers, I cant really use a symbol as it would give the answer away to the employee, I need a way to highlight the correct answer for the examiner only even if it is only on the pc. I'll keep searching lol

An alternative would be to add the correct answer (A, B, C or D) in a second column, then use the same formula with a different column as the first INDEX() argument, e.g. change $A$2:$A$500 to $B$2:$B$500 if column B contains the answer. This would also make looking up from a question number to an answer much faster, if you ever needed to do that, say if you were to input each students' answers in a spreadsheet and mark them using Excel.

 

[Jello]

Ok thanks mate I'll give it a try and see what happens