Topic and Quiz Spacing Spreadsheet: A Planning Tool for Teachers
By Yana Weinstein & Fabian Weinstein-Jones
Yana: When we first started the Learning Scientists project, we had some fascinating early conversations with teachers about how they might realistically integrate spaced practice into their teaching. While most teachers can see the value of spaced practice, many struggle to figure out the best way to implement this principle. My husband, excited to join the conversation but not a scientist himself, jumped in to help us out.
Fabian: I asked Yana whether my Excel macro-writing skills could come in useful for this project. She said yes, and the spreadsheet in this blog post is the result of our joint efforts – or, my efforts to interpret Yana’s sometimes confusing instructions, aka “INTERLEAVE ALL THE THINGS!”
Yana: The resulting tool is an Excel macro-enabled spreadsheet that allows teachers to create a spaced practice teaching schedule (1). Spaced practice here means that topics will be taught more than once, with repetitions spaced out over time. In addition, the spreadsheet creates further spaced practice by drawing quiz questions from a test bank. These quizzes provide both spacing and interleaved practice, as the quiz questions on any one day might come from any of the topics taught up to that point.
If you’re a teacher and you want to test drive the spreadsheet, you can go here to download it now. Then, you will need 3 numbers:
The number of different topics you will be teaching
Note: This can be as granular as you like, but if you have more topics than class meetings, you won’t cover all the material because the spreadsheet assumes that you are only introducing one new topic per class!
The number of class meetings you want to plan for
The number of quiz questions per topic that you will be able to provide
In addition, you will need your list of topics – but you can play with the spreadsheet by filling out dummy topic names. Here is an example of how you would input the information:
The first button you have to hit is “Create Quiz Bank”. This takes you to the second tab in the spreadsheet, where you will see a table of topics ready to be filled out with your own questions.
You can fill these out with your actual questions, or skip this step for now and let the spreadsheet populate your plan with the dummy questions.
Now you are ready to go back to the <Dashboard>, and hit <Distribute Topics and Quizzes>. What happens next is the magical algorithm, which I’ll let Fabian describe.
Fabian: First of all, each new topic is taught for the first time in its respective class (e.g., topic 4 will be taught for the first time in class 4). After that, the topic will be repeated using the Fibonacci sequence. That is, the Fibonacci sequence (starting at the second digit) defines the spacing gaps. Here are the first 5 numbers of the sequence:
1, 1, 2, 3, 5
So, if you have 10 classes total, topic 1 will be taught in the following classes:
Class 1
Class 2 (1 + 1)
Class 4 (2 + 2)
Class 7 (4 + 3)
The next time it would reappear is in Class 12 (7+5), but since you only have 10 class meetings, that won’t happen.
The same algorithm is then applied to each of the subsequent topics, with the constraint that any one gap between two repetitions cannot exceed the total number of topics. This is necessary because otherwise, the gaps would get too big and there would be some classes with no topics covered at all (in cases where the number of class meetings is much larger than the number of topics to be taught).
Here is an example output of the distribution of the first 6 class meetings. Notice that the new topic on each day is highlighted.
Yana: Because optimal lag depends on how far away the test is (2) and it is hard to precisely factor this parameter into curriculum design, we decided to go with the Fibonacci sequence as a simplified spacing algorithm with expanding gaps between each repetition (3).
The other part of the spreadsheet creates a quizzing schedule. You will see that the quizzes on each day cover (a) the current topic and (b) a selection of quiz questions from previous topics (these are selected at random from the pool of previously studied topics). As you can see below, quiz questions from the topic taught that day are highlighted.
By the end of the curriculum when all new topics have been covered, quizzes are made of questions randomly selected from across the whole quiz bank. This way, your students will have two opportunities for spaced practice: through revisiting topics during class, and on the quizzes!
To download the spreadsheet, please go here.
It’s important to note that this spreadsheet is not perfect even though it has already gone through many iterations. Also, although there is a huge amount of evidence for the benefits of spaced practice in general, this specific tool has not been compared to any other type of way of planning lessons and quizzes.
We are very grateful to Bryan and Ollie who tested out a previous version of the spreadsheet, and wrote about it on their blogs when it was still in early versions:
How do we know what to put on the quiz?
By Ollie Lovell, @ollie_lovell
Journey to Interleaved Practice #2
By Bryan Penfound, @BryanPenfound
References:
(1) Weinstein-Jones, F., & Weinstein, Y. (2017). Topic Spacing Spreadsheet for Teachers [Excel Macro]. Zenodo. http://doi.org/10.5281/zenodo.573764
(2) Cepeda, N. J., Vul, E., Rohrer, D., Wixted, J. T., & Pashler, H. (2008). Spacing effects in learning a temporal ridgeline of optimal retention. Psychological Science, 19, 1095-1102.
(3) Callender, A. A. (2010). Expanding retrieval increases long term retention by reducing rapid rates of forgetting. In S. Ohlsson & R. Catrambone (Eds.), Proceedings of the 32nd Annual Conference of the Cognitive Science Society (pp. 2123-2127). Austin, TX: Cognitive Science Society.
