Consulting - Quality
Difference makes the DIFFERENCE
Content - List
DEAR PARENTS
WE DO CARE FOR THEIR MARKS
I have prepared an excel file, which has three sections namely:
- Essay Questions,
this section may include all the questions where the answer may be more than 7 to 8 lines, with pictures at times, included in the questions. - Short Answers
this section may contain, short answers (where the answer may have one or two lines as response), Define the following, Name the following, give two examples for the following and all such similar questions. - Fill in the Blanks
this section may contain, fill in the blanks, objective type answers, choose the correct answer, match the following and all such one word answers.
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
Screen 1
Screen 2
Screen 3
First Function: RAND - Function
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.
Second Function: RANK.EQ - Function
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
Third Function: COUNTIF - Function
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
Fourth Function: VLOOK-UP - Function
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.
Output from VLOOK-UP - Function
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.
Final output should look similar to the below picture.
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.
The objective of this exercise, is to generate random question paper, each time, we conduct exam.
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:
- Select the area in which you want to generate random numbers
- Click on the button - displays a message box, where you need to type end number (refer to the following code, where I have fixed the start number to always be 1, so you just need to type the end number. For example, if you want to generate numbers from 1 to 30, you need to just type 30 ( in the following step) and the code will generate numbers from 1 to 30 but in random.
- Numbers are displayed in the selected column.
- The following is the code (for those, who can understand - also, this code is not mine, but I got it from internet.)
- There may be situation where you want to generate numbers from 15 to 30 instead from 1 to 30. In such case, uncoment the first line in the following code. (ie., remove the single quote before the word LOW, and run the program). Now it displays two dialog boxes, one for start number and the other for end number. Rest, everything remains same.
- Sample file provided for your reference. Open the excel file, right click on the button and select assign macro, list of macros will be displayed, select the existing macro to make it functional. Please click here to get the file that I used.
Sub randomNumbers()
'Low = Application.InputBox("Enter first valid value", Type:=1)
Low = 1
High = Application.InputBox("END NUMBER IN THE SERIES..", Type:=1)
Selection.Clear
For Each cell In Selection.Cells
If WorksheetFunction.CountA(Selection) = (High - Low + 1) Then Exit For
Do
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
Next
End Sub