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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

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
Back
Top