Add difficulty level on a working random quiz questions/answers

[freeofcost]

Hello

I have an excel quiz where questions and its answers both are randomized. I need help in getting it randomized based on difficulty level.

Please find the attached excel sheet.

1. It has a provision of shuffling the answers/options for every quiz questions that can be run with CTRL+SHIFT+A macro shortcut (code provided by Bernie Deitrick at http://www.excelforum.com/excel-prog...d-loop-it.html)
All questions are in column A. Every question has 4 answers and the correct answer is prefixed in question. Column B has the difficulty level.

2. It has a provision to generate 10 random questions out of 50 given in column A that can be run with CTRL+SHIFT+Q macro shortcut.

3. Both the codes are working fine. However, I need to put a difficulty level where first 5 questions should be 'easy', next 3 questions should be 'normal' and final 2 questions should be 'tough'. I need help in achieving this. Appreciate if I can get some assistance. Thank you

[Cofad]

In the while/wend loop in the ShuffleQuestions() sub-routine, you could add some tests and counts for the question toughness. Once the required number ofeach difficulty is found, then those difficulties could be skipped.

Try to put some code together, and I can take another look at it.

[freeofcost]

In the while/wend loop in the ShuffleQuestions() sub-routine, you could add some tests and counts for the question toughness. Once the required number ofeach difficulty is found, then those difficulties could be skipped.

Yes... thats the logic i tried. however, all my attempts failed...

Try to put some code together, and I can take another look at it.

I am trying to run the while/wend loop thrice... first loop to get 5 random questions that are easy using OFFSET and few other relevant functions to find the corresponding difficulty level, second loop to get 3 normal questions and third loop to get 2 tough questions. I am stuck at For While (qcnt <= 5) How can I tell the system to check 5 questions that are easy

[Cofad]

Maybe something like:

Please Login or Register  to view this content.

and then repeat similarly for med and hard. You'd have to read in the difficulty for each question first though.

[freeofcost]

You'd have to read in the difficulty for each question first though.

I am reading the difficulty for each question as Range("B" & (rndq(qcomplete) - 1) * 5 + 1) but not sure it is the correct way. I have been trying this from a couple of days. however, unable to put that as a condition in the while/wend loop. Also Is there a better way to do it instead of copy-pasting the same loop three time to get different types of questions (easy/normal/tough). Thanks

[Bernie Deitrick]

Try this to shuffle the questions, with tough, normal, then easy as the sort order. To change the order, change this line

.Range("C1").FormulaR1C1 = "=RAND()+IF(RC[-1]=""tough"",3,IF(RC[-1]=""normal"",2,1))"

to, for example, for easy, normal, tough order"

.Range("C1").FormulaR1C1 = "=RAND()+IF(RC[-1]=""tough"",1,IF(RC[-1]=""normal"",2,3))"

Not sure what you want to do with the 5, 3 and 2....

Please Login or Register  to view this content.

[freeofcost]

Thank you so much for your reply. Well... I was looking to modify the ShuffleQuestions module with a logic to pickup random questions without sorting the actual questions.

If you see the attached file, It has two modules:
1. ShuffleAnswers (A solution provided by you which I am going to keep at workbook open event so that answers are shuffled everytime the workbook is opened).
2. ShuffleQuestions - This module picks up 10 random questions from a list of 250 questions and it is working fine.

Your approach in the previous reply is to sort all questions based on difficultly level whereas I wanted a solution to pickup random questions based on difficulty level (modification to the ShuffleQuestions module). Something like While(qcnt <=5 and difficulty = "easy") an approach provided by Cofad.

[Bernie Deitrick]

Try this:

Please Login or Register  to view this content.

[freeofcost]

Thank you... Its a new logic and I liked the way you did it. I understood the concept of setting range of difficulty level in 'C' and finding the question block based on large random value and copy/paste. however, I am still trying to understand the syntax. I am learning a lot. I have observed that setting, finding, copying, pasting info [Select10Questions()] is time consuming compared to [ShuffleQuestions()]. I hope you have seen the attached file. Do you think editing the ShuffleQuestions() sub routine with Randomize function without changing the logic take similar amount of time?

[Bernie Deitrick]

If the macros work, it really doesn't matter what logic they use, or how efficient the code is. There really is not discernible time required for any of the macros, so I would not worry about it, unless you want to do it as a learning opportunity.