- 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,
- In the first column put questions, in the 2nd column, put question numbers, in the 3rd, points value of the question.
- 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.
- At the bottom of the question number column write the word "points"
- Below this in the question number column write the word "percent"
- 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.
- 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.
- 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
- Copy this formula to other cells in this column for each question on the test.
- Edit spreadsheet listing students' names in ONE ROW.
- 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.