Among the Microsoft Office Suite, Excel, Infopath, and Access all come
to mind as possibilities.
InfoPath might be great for administering a test with a fixed number
and set of questions, but may be difficult if you want to vary the
number of questions asked; I'm not sure if it has those capabilities.
Also, you'd have to dump the answers to a spreadsheet to grade the
test.
Using Access would provide you with the ability to build something
that looks very much like a piece of software. It would definitely be
able to handle the functionality you're looking for.
With a little tinkering, though, I think I have a good idea on a
bare-bones solution that you could build in Excel that would work very
well. In brief, here is my solution:
A) 4 columns for the test:
one hidden column to contain a 1 if the question will be asked
for this test or a 0 if not
a column for the text of the question itself
an answer column
and another hidden column which rates the answer
B) 1 column containing the multiple choices for each answer
C) Cells in the answer column use the Validation feature on the Data
menu to pick up the multiple choices from the 5th column. This forces
the user to fill in the cell with one of the multiple choice answers.
C) The worksheet must be protected so that the user can not unhide the
column which rates their answer as correct/incorrect.
E) The rating column uses a simple "if" statement to evaluate the
answer against the correct answer, and enters a 1 for a correct answer
or 0 for an incorrect answer.
F) A button or menu option for the user to select to "start" the test.
G) A macro which will pick a random number as the number of questions
to ask, then randomly put a 1 in the "include in test column" until
the desired number of questions has been included. Finally, the macro
could use the Filter command on the Data menu to show the user only
the questions they should answer.
H) The Pass/Fail grade is simply a determination of whether the # of
questions in the test (sum of the 1's in that column) divided by the
number of quetsions answered correctly (sum of the 1's in the rating
column) is greater or less than your pass/fail threshold.
Now the only problem with this solution is that the pass/fail grade
updates as the test taker goes along. So, they'll automatically know
if they answered the first question correctly, because their grade
will instantly be pass or fail. Then they could erase that answer and
go to question two and find out the correct answer, etc. So you may
want to incorporate the grading functinoality into a button that's
pressed which ensures answers for all the questions and protects the
cells against before dropping the simple division formula with the
pass/fail grade into the proper cell.
For a small fee (make me an offer) I'd be happy to build this for you.
Feel free to e-mail me for details.
Trevor
(e-mail address removed)
