"A Feedback Loop for the Classroom - Test Analysis a la Ruth Hoffman"

This Task Is Not For All Who Teach
      Don't bother with this page if all you wish to know about
  • how your students are doing and
  • what skills and topics they know and don't know,
is depicted in a passage like this in a grade book.
      This page involves more work and a focus on content on a micro-scale, a classroom scale.
      Good teachers do this analysis as they are grading papers. They note students grades and the relative difficulty of a specific test to prior tests. They watch who's done well with a problem and who's not yet mastered the material.
      Good teacher do what this procedure does only, the procedure makes what the teacher has learned easy for the teacher to recall at a later date, when the tests are returned, and to colleague not familiar with the students.
      It makes "concrete" what was once only known to the good teacher.

What the Test Says About the Students
      If you wish to consider how the test and the grades on the test are related, see then next page on curving a test.

You'll Need A Spread Sheet
      On the last page (and here on the left), points were recorded in the margin of the test. We'll use points for each question. We will also use a spread sheet and will create an analysis something like the one that follows.

Color Code Everything!
      With color coding and layout and statistics much is visible.
For each student, the analysis shows their:
  • grade on each question
  • overall raw score on the test
  • percent correct on the test
  • rank in the class
  • (where available) rank on the last analyzed test
  • dark green/blue/white cells shading
    what he got right, partially right, wrong
  • test z-score if desired
For the test, the analysis shows the:
  • statement of each question
  • response of each student to each question
  • point value of each question
  • grade of the class on each question
  • mean on the test
  • standard deviation for the test
  • ranking of difficulty of questions based on performance
  • dark green/blue/white cell shading analysis
    what everyone got right, partially right, wrong
  • individualized student achievement
      The big picture shows this.

See Growth and the Big Picture
      With color coding and layout and statistics much is visible.

The Steps
      It is easier to enter all data to the spreadsheet once the papers have been graded, so, grade the papers first and organize the questions points in the margins so they may be easily read.
      The spreadsheet work must be done before tests are returned to students. The ananlysis is most valuable if students are permitted to see it. In many public schools this is not permitted. In 2- and 4-year colleges, it is.
      If comments to a class about "how they did" are not permitted, I guess it doesn't matter.
    To analyse the test using a color-coded spsreadsheet, do these things,
  1. In the first column put questions, in the 2nd column, put question numbers, in the 3rd, points value of the question.
  2. At the bottom of the points column, in a cell we'll call (cellpoints), use the formula "=sum(cellfirst:celllast)" to get the total number of points for the test.

  3. At the bottom of the question number column write the word "points"
  4. Below this in the question number column write the word "percent"
  5. To the right of this, below the first formula, in a cell we'll call (cellpercent), write the formula
          "=(cellpoints)/(number of points)*100"
    to compute the percent correct.

  6. Select the cells called (cellpoints) and (cellpercent) and paste them to the other columns in the same rows.
    This computes individual student performance on the test.

  7. To make the cell which computes the class's performance on a question, another formula is needed,
    "=sum(cellfirst:celllast)/(points)/(numbers of students)"
        See the graphic below and here for student performance.
  8. Copy this formula to other cells in this column for each question on the test.
  9. Edit spreadsheet listing students' names in ONE ROW.
  10. If you set up the spread sheet as described, as you enter a student's work, the points a student has earned and the percent correct change until the last point value is added. So, in the column matching a student's name, record the student's points. Repeat this for each student.

Mean and Standard Deviation Permit One to See The Bigger Picture
      Thus far we've dealt with the details. Next we work on the bigger picture: compute the mean and standard deviation.
  1. Label and create a cell for the mean (arithmetic average) using the formula
    where the cells are the STUDENTS' AVERAGE GRADES.
  1. Label and create a cell for the standard deviation (average spread) using the formula
    where the cells are the STUDENTS' AVERAGE GRADES.

Click below on "Formulas Cheat Sheet"
for the formulas used thurfar
or press cheatsheet.

Color Code and Sort as You Analyze
      Two sorts and a color-coding are required to complete our task.
      As you have been working, you've noted how students performed and what questions were tough. We'll continue this analysis as we sort and color.
      It really doesn't matter which is done first.
  1. Sort the students & their scores. Highest is leftmost. Decreasing in mastery from the highest to the lowest.

  2. Rearrange the rows of questions using the mastery (%) column. Put the hardest question (poorest score) on the top.

  3. Shade dark for full mastery, shade light for 50% or better, do not shade for performance of less than 50%.

  4. If you wish to look at creating a better test or more about the mean and standard deviation and z-scores, check the table of contents.


Below is the analysis in full. Click on it to enlarge it.

Additional Stuff
      A spread sheet is provided for you to play with and edit. It has 2 versions of a 10-student form -- no formulas included and formulas already in place.
      Additional info on grading, averages, curving, and test anlalyis may be found in the Table of Contents.

mathnstuff.com/papers/craft/c26.htm © 2011, A2
"A Feedback Loop for the Classroom - Test Ananysis a la Ruth Hoffman"
[MC,i. Home, site home page] [master list of all MSH! dictionary words] [tutorials & resource material arranged by topic [Good Stuff -- free & valuable resources] digital manipulatives math games letters from the picnic table in an ivory tower