I worked on this project last year so that I could better use the data I was generating from quizzes. I wanted a way to do targeted remediation to a large number of students in an individualized way. Getting students to teach other students was the best way to reach the largest number, so I set up this spreadsheet to make life easier. The macros I wrote eliminate the time between grade entry and targeted remediation. Instead of pouring over tests or handwriting lists, you can grade a large number of tests in very short time and use the auto-generated lists to provide individualized remediation.
The spreadsheet includes tools to
- Grade multiple choice tests and split scores into an arbitrarily large number of benchmarks
- Analyze the results of a test and provide results broken out by benchmark for all students
- Process students scores for open response questions and handle questions that are open ended or multiple choice
Most of the data below was generated by the macro. The only work required on my end was to do a few clicks to setup the spreadsheet and then to enter the student’s answers.
The data entry sheet
Another click generates a list I use for strategic peer tutoring. Basically you get sorted lists of students who did well on each benchmark and students who need to review it. For all students you see every mistake. If you spend more time entering comments about the mistakes at grade time, the comments will be sorted and kept with the student on the peer review sheet.
A macro generates lists for remediation
If you’re interested in using the spreadsheets, I set up a page with the excel 2007 – which even includes a misspelling of Jack Bauer acting as John Doe #1. I hope you enjoy.
Go here for the template and a screencast.
What’s a benchmark in this context?
I’ve worked in a few places that divided the curriculum into standards and benchmarks. The standards were always general areas like Number Sense or Geometry. The benchmarks are bite-size chunks from that general umbrella like “Students can find the area of irregular polygons.”
With the spreadsheet though, benchmarks can be any concept that you are assessing on one or more questions. I usually think of it as a tiny objective. Something like, “the student can multiply fractions” would be one benchmark, and “the student can multiply mixed numbers” might be another. That way, remediation becomes more targeted and specific, and I am able to accurately assign student helpers who know the skill and can explain it to another student. You could break your test up into more general benchmarks, and group more of the questions into the same benchmark.