Difference makes the DIFFERENCE
Content - List
I have prepared an excel file, which has three sections namely:
Full sheet view (fig 1)
Essay Questions section (in ref: to video - Section 1
This section can have any number of questions, I have given a sample for over 15 questions.
All these questions are of essay type. I have included, questions from nearly 8 chapters.
V-LookUp function takes care of picking the question.
Like wise with the other two screens
Col B will have the list of questions and Col A will have the serial number, therefore, place RAND() in Col D as shown in the figure below. The purpose of this function is to generate numbers between 0 and 1. If technically, unknown, don't worry, it does its job.
Drag down this formula till the last row as shown in fig 1 (first figure). For all the selected column, the function generates some random numbers.
Place this formula in Col E as shown in the figure. The purpose of this function is to rank, (just similar to the rank we get in school days) the numbers generated by RAND function. Drag down this formula till the last row as shown in fig 1 (first figure). For all the selected cells in the column, the function generates a unique number, after comparing the current number with other numbers in the selection.
Take care that there is a $ sign as shown in the picture, else the formula becomes relative
Place this formula in Col C (with label - dups - in short, for duplicates) as shown in the figure.
The purpose of this function is to count, if there exists any duplicates from RAND function. (I have tried with over one lakh rows and did not find any duplicates, so we need not worry)
The whole objective of using this formula is to avoid any duplicate questions in the question paper. - If repetitive questions is not a concern, you can ignore this formula.
Drag down this formula till the last row as shown in fig 1 (first figure). For all the selected column, the function generates some random numbers. Take care that there is a $ sign as shown in the picture, else the formula becomes relative
Place this function where, you want to get the question from the set of questions.
The task / job of this function is to get the question, corresponding to the number generated by RANK function (for example, if in Col E and row 2, we have '8', then, this function should get question 8 from column B and place in Col G.
This function is a bit complicated, I am also confused quiet often as to where to check and where to put, based on what condition, but don't worry, we always suceed.
Place So, if every thing is done, we can, print the question paper by setting the "Print area" from Page Layout Menu in Excel and ask you child to answer the questions.
You can extend the same concept to Science, Social, English and all other subjects.
Thank you for your time and your interest in your child.
If you have experimented and failed, and wish to see my file,
please let me know, I will send it to you.
This can also be quiet useful, in other areas where, the knowledge of a person should be put to test, based on some training, or, same questions jumbled cross 10 different people, or, when we want to have 10 different people, having the 10 different questions, which may be from the same question base, and other such uses.
I believe, we, as parents are responsible in one way or the other, for the performance and behaviour of our children at school.
The objective of this page is to help parents, who inturn can help their children in getting good marks, by the method of practice and practice at home, before they takeup final exam.
Regarding questions and grouping...:
For the people, who know how to code or can understand coding with Excel for VBA, the following files can be pretty useful. I placed a button to generate random numbers.
Procedure to work:
'Low = Application.InputBox("Enter first valid value", Type:=1)
Low = 1
High = Application.InputBox("END NUMBER IN THE SERIES..", Type:=1)
For Each cell In Selection.Cells
If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
rndNumber = Int((High - Low + 1) * Rnd() + Low)
Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
cell.Value = rndNumber
Application.StatusBar = "Working with... " & cell.Address
Sorry to say that, I am not able to upload file with macro, Please follow the steps given above to enable a normal .xls or .xlsx file to convert to .xlsm file (file with macro)