Check Out the Hook While My Data Revolves It
Comrades! Fellow Humans! Welcome! I know it’s been a while, but …
I spent most of the summer on this, so I wanted to share it. A much more entertaining post will follow soon with the trials and tribulations of the year thus far. One of the biggest thorns in my side has been the fact that along with teaching 3 preps (2 new to me, and all by myself), I also am the data coordinator for my school. What does that mean? I don’t know, really, but somehow I was picked to be it because I knew how to do text to columns in excel, I think. But ANYWAYS, over the summer I taught myself some excel and developed this spreadsheet for my staff. Here is an example of a BADASS formula that I had to create:
=IF(D$41<>””,IF($B4<>””,IF(D$3<>””,SUM(IF(‘Data Grid (2)’!$F$9:$BC$9 = D$3, (IF(‘Data Grid (2)’!$F12:$BC12 = ‘Data Grid (2)’!$F$11:$BC$11, 1, 0)), 0))/COUNTIF(‘Data Grid (2)’!$F$9:$BC$9,D$3),””),””),””)
So, some caveats:
1.) If you are better at excel than me, then please help. I will take any critiques or advice on how to make this better, or at least more efficient. My main concern is that most people on my staff are stupid when it comes to excel, and rather than click on the help menu, they will bitch about being stupid with excel and complain about it until I come and fix it for them, so I tried to make it as idiot-friendly as possible.
2.) Because I work with people who are excel idiots, I locked everything down. If you would like to use this for your class or distribute this to other teachers in your school, but would like to change some stuff (for instance, it can only handle 50 questions or 35 students in each class) the password is “buttface.” Classy, I am not.
3.) A much larger discussion (and blog post) is needed to discuss the merits of using a large interim assessment within the classroom setting. Just for posterity’s sake, full disclosure:
-I have gone to SBG, so I don’t really give multiple choice tests anymore. I hope that you don’t really either. BUT: my state test is MC, so on occasion I give a practice Regents exam and use this to help analyze the data.
-It is helpful for teachers who are not SBG to still view tests by topic rather than as a whole. This spreadsheet is sort of a compromise for those who want to still give MC tests, since it still will break down the test not only by each question, but aggregate the different topics and show how your class did as a whole on the topic AND how each individual student did on the topic. In the past, I would give a small quiz a couple of times a week and tracked the data over time. To be honest, because this is based on averaging data, it doesn’t show growth very well if you keep saving into the same spreadsheet. I use different spreadsheets for each assessment I give.
-Our school requires that teachers give 4 interim assessments throughout the year. These are formative assessments, which for most would mean that it does not count as a grade but is merely used to assess where students have trouble and help them fix those deficiencies. That is how I would use this tool. It is easy to identify where each student is struggling so that you can break your class into groups and have them work on specific skills using whatever resources are available to you.
So, anyways, here are the instructions I sent out to my staff. Just so you know, the first bit are specific instructions for how to run the tests through the scantron and copy and paste them into the spreadsheet. If you don’t have those capabilities, it really doesn’t take that long to type in student responses by hand, if you use the 1-2-3-4 option rather than a-b-c-d.
Oh yeah, and I’m also a real jackass, especially to my coworkers. Excuse the occasional biting tone.
After you’ve looked that over, here is the actual spreadsheet file (remember, all of the formulas are locked down. Play around for a bit. If you need to change something, the password is buttface:
(and, of course, there are plenty of easter eggs hiding in the spreadsheet. If you leave it locked, all kinds of fun error messages pop up).
AND, to help you see how it can be used, here is an example of a spreadsheet that has been filled in (the first page is actual data from my physics class, the other two tabs have fake data put in there)
For real, though… no one ever taught me how to do this, so I’ve just sort of developed something that makes sense to me. If you have any better ideas that can easily be implemented for an entire staff, let me know. And again, we are required to do data analysis on interim assessments at our school, so saying “data sucks” is not helpful.