random question generator

southernsquid

Board Regular
Joined
May 19, 2011
Messages
60
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!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

shadow12345

Well-known Member
Joined
May 10, 2004
Messages
1,238
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

Board Regular
Joined
May 19, 2011
Messages
60
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

Well-known Member
Joined
May 10, 2004
Messages
1,238
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

Board Regular
Joined
May 19, 2011
Messages
60
Too Easy!! Wow, I feel stupid. Thanks for all your help Shadow! I really appreciate it.

Southernsquid
 

anandvarma

Active Member
Joined
Apr 26, 2011
Messages
384
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
=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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,266
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.
 

Jello

New Member
Joined
Jul 7, 2015
Messages
5
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????
 

Forum statistics

Threads
1,175,656
Messages
5,898,691
Members
434,723
Latest member
Epeople

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top