Coherence Gap Spreadsheet

I'm overdue in getting this out into the wider world, but I've developed a spreadsheet that incorporates all the coherence connections found in the Coherence Map and adds to that instructional time data about how many lessons and hours a math curriculum spends on each standard. Yeah, it's a lot. But as I talked to math teachers and leaders at the end of last spring, I felt there were a lack of tools available that incorporated both coherence and instructional time, and my solution was a big Excel workbook with lots and lots of rows of lesson data, some long VLOOKUP formulas, and some conditional formatting to make the results readable. If you geek out over spreadsheets and math curriculum planning, I think you'll like it.

The lesson data comes from EngageNY for K-5 and Illustrative Mathematics for Grades 6 through Algebra 2. I didn't have any special preference for one set of curriculum materials over any other (and neither does the State of Colorado), but both of these are open educational resources with lesson alignments and time estimates, so I used them. You can substitute time data in for whatever materials you'd like, but be warned that you're looking at 3500+ rows of data, so either bring a team of people with you or learn to write some code that scrapes data from publishers' websites and formats it for you. (I chose the latter.)

With the help of a pivot table and some lookup functions, what this spreadsheet allows you to do is indicate some percentage of coverage you thought each standard has gotten (if making sense of past curriculum decisions) or will get (if planning for future curriculum decisions). In return, the spreadsheet reports back to you how much instruction for each standard is left unfinished, and how much future instruction (following arrows in the Coherence Map) might be at risk. Just be mindful that the spreadsheet is like a lot of models—wrong, but possibly useful. The instructional time estimates might be flawed, not everything aligns as neatly as I'd like, the data may not really reflect your materials or pacing, and the crude way the coverage formulas work might just be wrong. So if it gives you some data that you just know is flawed, then maybe it is. But if you're patient with it, and you aren't afraid to look beyond the conditional formatting color scheme and dig more deeply into why instructional time is allocated where it is, then I think this spreadsheet can give you something to work with as you make decisions about your curriculum planning.

This was one of my on-the-job projects as math specialist for the Colorado Department of Education, so CDE is hosting the spreadsheet itself. Head on over to the Coherence Gap Spreadsheet page to download the latest version of the file. I also urge you to watch the tutorial video, which I'll also embed here. I kept it as short as I could at 12 minutes, and if you're already familiar with the Coherence Map you can skip the first 2:00.

If you have any questions about the spreadsheet, please let me know. And if you modify the spreadsheet to make it better or more inclusive of more curriculum materials, I'd really like to know about that, too.