Hi All
Ver 3.1
Is there a quick way to see (or calculate) the average time that the class took to complete the Quiz? If it is not in the normal statistics, does anyone know how to run a quick conversion in Excel to convert the text (e.g. "1 hour 21 mins" and "57 mins 18 secs") directly into seconds which can then be averaged?
Thanks
Regards
Although I believe "average time on exam" is meaningless, I will try to answer your Excel question.
To convert "1 hour 21 mins" and "57 mins 18 secs" I would use "Text to Columns" and pick "space" as the delimiter.
Hi Rick
Thanks for this. My problem is that I created a new exam format, and I'm anticipating that some Administrator may query whether or not I allowed enough time for the exam. Now, I can answer what I observed, that all but one of the students completed the exam before time, that many students completed well before time, and that the one student that ran full time took more than 20 minutes to answer the last three questions (MCQs), just going back and forth reading and re-reading the questions until she finally had to settle on an answer before running out of time. (I think if I had given that last student an extra hour, she would have used it as well, hoping for some extra insight).
That description is enough to assure most educators that the time allowed was enough. Unfortunately, that answer cannot be reduced to a single number. It helps more if I can show that I allowed 2 hours for the exam, 193 students took the exam, and the average time was 1 hour and 4 minutes. (If necessary, with other stats also).
In answer to your solution - it sort of worked, but only really works if all candidates took more than 1 hour. If some took less than an hour, then the display is in minutes and seconds, and so the split across the columns is different.
But your solution gave me another idea: I used the Text to columns to split the Date and time of the Started and Completed times, and then saw that the separated times were in Excel's time format, and I could subtract one from the other. Then I figured, well, if the separated times are in Excel format, then maybe the original Date/time is also in proper Excel format, and, if so, I can simply subtract the Start from the Completed. And it works. It give the answer in days, so a simple multiplication by 24 and 60 (=((SUM(I2-H2)*24)*60)) gives the time in minutes, and that is a simple number, so I can find the mean of that.
Thanks very much.
Regards
Yep Ken, it looks like you are using Excel correctly and that you have discovered that you can calculate average time, but it takes a little extra care and manipulation in Excel. Hopefully your administrators don't want you to do this too often. (Or better yet, give the administrators your moodle file and tell them to do the calculations themselves, since they seem to have extra time on their hands .
Realize, of course, that "time" is not totally accurate. Let's say you have a smart student who finishes your exam in 10 minutes, but chooses not to submit until 1:55 hours. Did this student spend two hours on the exam? Also, (as long as you have the data in Excel) run a correlation analysis for time and grade. When I do this, the correlation is very low. In fact, I have found the regressed straight line to have a negative slope.
But you still have reason to explore. I ask a different kind of question, however. I am more concerned about the "minimum time." If I design an example that should take the average student 1 hour, allowing 2 hours, and find that all students finish the exam in under 10 minutes, then I can conclude that the exam is too easy. So I find that the analytics with "minimum" time might be more valid than the analytics associated with "maximum" time.
My approach is to create a "valid" one-hour exam and then give students two-hours to complete it. I see how students do overall, and then I make adjustments so that the grade distribution is where I want it to be.
The numbers are interesting to look at, but they do have inherent errors with them.
Well, I think that you are well on your way to calculation what you want. If you know SQL and have access to your moodle database, you could probably do all of this without using Excel.
