Tests are good because they quickly demonstrate to students the level of their preparation in a particular area of knowledge. Teachers, on the other hand, have to spend time, like decades ago, on manual processing of results. You can automate the process and unload teachers using Excel.
Instructions
Step 1
Prepare three questions and answer options in order to master the composition of the test using a small amount of data. Having dealt with a simple example, by analogy, you can use the Excel spreadsheet editor to develop advanced options.
Step 2
In an Excel worksheet, combine several cells that will contain the first question in the test. Fill this area with some color to make it look nice.
Step 3
Type the text of the first question in the prepared box. Right-click on this cell and select Format Cells in the context menu that appears. On the Alignment tab, specify how the text should be positioned horizontally and vertically. For example, you can select Center vertically so that text does not snuggle up to the top or bottom of the cell. Check the "Wrap by words" checkbox and click the "OK" button.
Step 4
Combine a few more cells to create space for the answer to the first question in the test. Also fill this place with the color you like so that everything looks harmonious.
Step 5
Copy with the mouse the prepared cells for the first question and answer. Paste them twice into an Excel worksheet to get space for the second and third questions and answers. Type the remaining questions in the appropriate places. The external design is ready.
Step 6
Select the cell where the answer to the first question in the test should be located. In the upper horizontal menu of the program, select the item "Data", and in it - "Check …". On the "Parameters" tab of the window that appears, specify the "List" data type. Check the boxes next to Ignore Blank Cells and List of Allowed Values. In the "Source" field, list all possible answers to the first question, separating them with a semicolon. The student will have to choose the correct answer from them.
Step 7
Similarly, in the appropriate cells, create lists with answers to the second and third test questions.
Step 8
On a separate sheet, use the boolean IF function to automatically calculate the results. Set up the function arguments by specifying the cell from which the answer was selected in the "Log_expression" field. In the "Value_if_true" field, enter the word "Correct" in quotation marks. In the "Value_if_false" field, enter the word "Error" in quotation marks. Do this separately for the three answers to the test questions.
Step 9
To calculate the total number of correct answers, use the Boolean COUNTIF function. Customize the arguments to this function. To do this, in the "Range" field, specify all the cells with the answers to the questions. In the "Criterion" field, enter the word "Correct" in quotation marks.
Step 10
Save the results of your work and check how accurately the program calculates the number of correct answers.
