How to Analyze Pre and Post Test Data

Instructions for Analyzing Pre and Post Test Data

Log in to Checkbox.

Select the survey data you want to analyze.

Select to export the data. In the Export window, under CSV options, select all options but the last one.   Then export the data as a normal CSV file.

Open the exported file in Excel.  Delete excess columns, keeping the data columns, the response number and the time (which is useful if you want to see if, e.g., students respond to the questions faster.)  The select all of the data, and sort it by netid.

Delete extra responses with no data. 

Some students confused netid with student id. (Revise so this is clear for the fall.) Go to student registration list (e.g., PhysicsS10) in directory?  and look up the netid corresponding to each student id, and replace in the data file. 

Delete duplicate entries for each netid. E.g., student may have only partially completed the test and the logged out and back in, creating two entries.  Use  =If(D2=D3, True,) to highlight the duplicates.

Now that you have the raw data cleaned up, cut and paste it into the Standard Response sheet of the appropriate Converter (e.g., FMCE Converter) located in Survey Analysis Templates.  Save total time for future use.  The Converter formats the data for you, replacing strings with letters and lower case with uppercase.  The formatted data appears to the right of the data you cut and paste. 

Select the formatted data, and cut and paste into a file you’ve created for that survey, course and semester, e.g. FMCE-P1112-S10.  Create this file using a template found in Survey Analysis Templates, and paste the data into the sheet ???

Sort this data by number of questions answered  ???, and throw out any that haven’t finished the survey. Do this by just looking and deleting.    Published literature uses if they’ve answered 50% or more. 

Copy this data back into FMCE Converter, onto the sheet FMCE Scorer.
Then copy the Total score from the right hand column into the file you’ve created for that survey/course/semester, e.g.,  FMCE –P1112-S10,  into the right hand column,  for Pre or Post data, depending on what you’re working on.

Go to the Scorer worksheet in the Converter.  Copy from the top table (percent correct for each answer) into the FMCE Analyzed worksheet of the FMCE-P1112-S10 file.

Go to the FMCE Part Analyzer worksheet in the Converter, and paste the same information  here. 

Once you’ve entered both the pre and post-test data:

Go the FMCE Analyzed worksheet of the FMCE-P1112-S10 file.  Look at the pre and post data.  You now want to look for matched pairs of pre and post data – where the same student has completed both tests.  Copy the student netids from the posttest list into the pretest list.  In the right-hand column, enter =match(top netid, $topnetid:bottomnetid$,0), and copy down.  This will return a number corresponding the row number where the match is found.  

Highlight ones that are matched in a different color. 

Sort the data using the numbers generated.   

Copy from P1112 Post Score Total into Analyzed – FMCE Score Total Post.  Then do same with Pre.   You now have your matched pre and post data. 

Calculate the normalized gains.  There are two ways to do this:

1.   Evaluate the average pre-test score (over all students) and the average post-test score. 
Then calculate  Gain = (Class Avg Post – Class Avg Pre) / (Total Possible Score  – Class Avg Pre).  This gives the Average Normalized Gain. 

2.  Copy the Pre and Post scores (for the entire test, not for the individual questions) into the FMCE Score Matched worksheet.  Then calculate the normalized gain (Student Post – Student Pre) / (Total Possible – Student Pre) for each student.  The average these scores over the entire class.  The result is the Standard Normalized Gain. 

Then do your histograms and generate graphs.

Finally, generate reports for the instructors using previous reports as a template.  The previous reports are found in Spring 2010 Survey Data.