Timing Quizzes in Excel

In my previous post, I had described the use of data validation and cell protection features of Excel to create multiple choice quizzes. While having students take such quizzes, it would be nice to know how much time a student spent on taking the quiz. In this post I will show you how to accomplish this. I will illustrate this with reference to the quiz that was created in the previous post. This will be  good time to go over that post if you haven’t already done so.

The basic idea behind timing the quiz is as follows. We will generate a time stamp when the quiz-taker first answers any of the multiple choice question. This time stamp will signify the start time of the quiz. The time stamp will be stored in a hidden sheet so that the quiz-taker is unaware of it. Referring to the earlier post where I described quiz creation, the worksheet containing the lists of answers for different questions was made a hidden sheet. So let us assume that we will store the start time stamp in this sheet. One possible way to generate the start time stamp is to use the following formula:

=IF(COUNTIF(Quiz!E6:E14,”Click to answer”)<5,IF(A16=0,NOW(),A16),0)

This formula uses a circular reference in cell A16 where the starting time stamp value is to be stored. The condition part in the above formula checks for whether any question has been answered or not by counting the number of cells in the quiz sheet that have “Click to answer” as content. The counting is being done over the cell range Quiz!E6:E14 where all the five quiz questions appear. As long as no question has been answered, cell A16 continues to contain 0; however a time stamp is created using the NOW() function the instant a question is answered. The inner IF in the above formula ensures that only one time stamp is generated. Remember, the calculation mode for the workbook must be set to manual for circular referencing to work.

To generate the time stamp signifying completion of the quiz, we will make use of the cell Quiz!E16 status where a quiz taker is expected to change the cell value from “Not Done” to “Done” at the end of the quiz. The end time stamp in cell A17 thus can be generated using the following formula:

=IF(Quiz!E16=”Done”,IF(A17=0,NOW(),A17),0)

To figure out the time taken by a quiz taker, we simply calculate =A17-A16 in a cell and display the result in hours and minutes using the custom cell formatting shown below.

Thus, with two simple formulas we can calculate the amount of time a quiz taker has spent on completing the quiz.

Technorati Tags: Time-stamp,NOW function,Custom cell format,circular reference,COUNTIF function,Timing quizzes