CONSTRUCTION OF TESTS USING COMPUTER SPREADSHEETS

INTRODUCTION

When course subject matter can be tested by problems and questions with numeric solutions, spreadsheet software can be used to construct and print new multiple-choice tests relatively quickly once the basic test is constructed using the spreadsheet. All questions need not have numeric answers, but this type question provides the most savings in time and effort when making new tests from the basic test model. The construction of a computer generated test is divided into three parts: the statement of the test problem and/or question, the test problem computer solution, and the multiple-choice computer generated responses. All three parts become a spreadsheet template for making new7 tests.

STATEMENT OF THE TEST PROBLEM/QUESTION

All the figures accompanying this paper provide examples of test problem statements and questions generated with spreadsheet software. The row numbers and column letters are provided for reference to location of information cells and are not printed on the student's test question sheet.

Numeric data manipulated by formula

The spreadsheet allows problems and questions to be stated in sentence format, but numeric data that are manipulated by formulas must be in unique cells.In Figure 4, the data in cells E137 through E140 are manipulated by formula to create correct and incorrect responses to the multiple­choice questions and are therefore in unique cells. If the problem solution is lengthy, the data can be entered and manipulated at a separate location on the spreadsheet. This will be discussed later under problem solution.

Data changes for new tests

Data in the problem statement or in the question which will change on future tests should preferably be in unique cells so that the data may be changed efficiently. Otherwise, the changed data will require that the sentences be edited in addition to changing the data in the unique cells to be manipulated by formula.

In Figure 1, the data in cells G9,G11, and G15 are in unique cells because they will be changed when constructing a new test. The new data are entered at cells N4,N6, and N11. Cell 123 in question 3 is unique and is updated automatically from cell Q23 in Figure 1, so that the question's answer is not dependent on the student's solution of book value. In Figure 3, the numeric data in question 17 are not in unique cells and must be edited in the sentences if they are to be changed for a new test. The new data for question 17 are entered in the problem solution cells N88, 89, and 90. A note is provided near the newly entered data to remind the author to edit the question statement at cell 8116.

In Figure 7, any of the financial accounts may be varied in dollar amount for a new test, and the totals, financial ratios, and any question responses dependent on the changed variables will automatically change.

Questions without numeric answers

The spreadsheet may contain questions which do not have numeric answers. Figure 4, question 27, and Figure 6, questions 1 and 2 are examples. Use of a spreadsheet to generate non-numeric questions has no advantage over word­processor software. Therefore, the non­numeric questions should only be included if necessary to compliment testing of numeric solutions to the subject matter.

Variation between tests

New tests are generated primarily by changing the numeric value of given data. Cells which contain data that will be updated with each new test are "un-protected" and all other cells are "protected". This aids in visually determining where new data is to be entered because the "un-protected" cells are highlighted on the monitor screen. A box has been placed around "un­protected" cells in each figure to aid the reader in determining where new data will be entered.

Alternate questions may be placed on the spreadsheet as shown in Figure 6 and may be exchanged with previously used questions to provide variation between tests. Additional variation between tests is achieved by editing and reordering questions and responses. Some reordering is necessary to change the key for correct responses to the questions.

Question dependency

It is desirable on a computer-graded, multiple-choice test to make all the questions independent of each other, but this is difficult if not impossible to do on lengthy problems. This situation may be seen in Figures 1,2 and 3. In Figure 1, the correct response to question 2 is dependent on having the correct response to question 1. The remaining four questions in Figure 1 have solutions that are independent of the other questions. In Figure 2, the correct response to question 9 is dependent on knowing the correct answers to questions 7 and 8. In Figure 3, the correct response to question 15 depends on knowing the correct response to question 14.

A solution to the question dependency problem is to create many short independent problems. This was done for the equipment problems in Figure 5. Yet, there is merit in testing the "bottom line" answer as well as the subpart answers even though the "bottom line" answer is dependent on the subparts.

PROBLEM/QUESTION SOLUTION

All numeric data must be available in unique cells for manipulation by formulas designed to solve the problem or subparts of the problem. As an example, in Figure 1, cell N7 contains the formula 0.2*N4 or 0.2 times the content of cell N4. Cell N4 contains the numeric value 500,000., and the value of the formula, 100,000., is produced in cell N7. The formula in cell N12 could be either +N4-N7 or O.8*N4. In either case the value in cell N12 is 400,000. Additional representative cell formulas are provided in Figures 1 through 5.

Short test problems

If the problem solution is not lengthy, the formulas for correct and incorrect responses may be in the question response cells. This is the case in Figure 2, questions 8, and in Figure 4, question 21. Each question response cell contains a formula to generate the response based on data in the problem statement and is independent of the problem solution.

Lengthy test problems

When the problem solution is lengthy and can generate many test questions on the subparts, it is desirable to solve the total problem at a location on the spreadsheet separate from the test problem statement and questions. The test problem is designed so that new values for the variable parameters are entered at the problem solution cell block, and the variables in the problem statements and questions are automatically changed if they are in unique cells.

Figures 1, 2, and 3 contain problems of this type. In each case the problem is solved in its entirety, even though the student may not have to solve the complete problem to answer the test question. Having the entire problem solved with all the intermediate steps of the solution in one area of the spreadsheet aids in determining appropriate subpart questions and responses. Another advantage to this technique is that the entire problem solution can be printed separately from the test and used to review the problem with the students after the test.

Homework exercises

Homework exercises may also be generated easily from the problem solution format on the spreadsheet. Figure 6 provides an example, although the lengthy problem solutions in all the examples provided are used by the author to produce homework exercises.

MULTIPLE-CHOICE RESPONSES:

All the multiple-choice responses are in unique cells which contain solution formulas to either provide a numeric answer or accept a numeric answer from a unique cell in the solution if it is at another area of the spreadsheet. In Figure 4, the correct response to question 21 is C for response cell G143 which contains the formula +E138/(l­E139-E140). Incorrect responses preferably contain typical student errors, but in this case the incorrect answers, A and B, are 60 and 80 percent respectively of the correct answer. Incorrect response D is generated by the formula (E139+E40)*El37+E138 which contains a typical student error.

CONCLUSION

Computer spreadsheet software is a very useful tool for generating multiple-choice test questions, correct and incorrect responses, and solutions for problems which have numeric answers. Tests, problem solutions, and homework exercises are generated rapidly and easily, providing considerable time savings for teachers.

REFERENCES