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!

 

[shadow12345]

To get a random question you from a list in col A put this in Col B

=INDEX(A2:A500,ROUND(RAND()*COUNTA(A2:A500),0))

 

[southernsquid]

Please understand, I am a novice. How do I tell it I want 50 questions. Can u please explain it to me in great detail? Thanks Shadow

Southernsquid

 

[shadow12345]

Ok fair enough no problem.

1. Put the formula in cell B2 (questions start in A2)
2. Hightlight the cells B2 upto B50
3. Press Ctrl + D

 

[southernsquid]

Too Easy!! Wow, I feel stupid. Thanks for all your help Shadow! I really appreciate it.

Southernsquid

 

[anandvarma]

=INDEX(A2:A500,ROUND(RAND()*COUNTA(A2:A500),0))
the problem with the above formula is there will be dulipcate in the question bank, to avoid that use MRAND function in the formula

 

[mikerickson]

If the questions are in column A and you want to select 50 questions at random you could put

=RAND()<(50/COUNTA(A:A)) in the cells of a helper column and filter that on TRUE.

 

[southernsquid]

Thanks for the help guys. What is a helper column and how do I filter that on True?

 

[southernsquid]

Any ideas for multiple choice tests?

 

[Jello]

To get a random question you from a list in col A put this in Col B

=INDEX(A2:A500,ROUND(RAND()*COUNTA(A2:A500),0))

ok this works perfectly for what I am doing the only thing I need is to keep the highlighted text from the original question onto the random selection questions any ideas????