I’m baaaaaaaaaaaaack! After a long break, the longest I’ve had in the four summers since I began teaching it’s time to get back in the swing of things. I joined in the scheduling of my small school (110 kids in 7/8 grade). After spending time understanding the beast and more time writing macro code for the god-of-scheduling spreadsheet, I thought I’d share the outcome.
I found it exceptionally useful to add lots of
countif formulas into the spreadsheet. Keeping track of the number of students in a class, the number of students scheduled for a given period, and the number of students a given teacher will be seeing provides three ways to check that the schedule is not broken. In our school, with one teacher per subject per grade, it means that each teacher’s schedule and each period’s total number of students scheduled should be exactly equal. Here’s the table that did that.
Our schedule consists of five core classes and two electives (which contain five distinct blocks on the schedule). This works out so that each student must be assigned a class for 10 different blocks. Hence the periods P1 through P5 and the five elective spots that come afterward.
The part I’m most proud of is the macro that generates class lists. Using a bit of algebra to get things properly arranged on the class lists sheet, it takes all the student schedule data and re-arranges it into class lists for teachers with first, last, and full names (so folks get their preference) as well as gender counts to help you make adjustments. It’s nice to have the class lists linked directly to the source of the data so that if you make a change to a students’ schedules you can simply click the yellow “generate class lists” button and the class lists will reflect the students schedules perfectly. In my case the button saves me from making roughly 70 individual class lists.
Resource is here. Unfortunately you’ll need Excel 2007. Make sure to enable macros.
As for a bit of reflection, having spent a lot of my time up to the first day of school working on scheduling I have a bunch of respect for everybody else who’s done it.