Assessssv3.xls provides individual and entire class item analysis, especially helpful after pre-testing to determine instructional focus areas. Here is an overview and instructions.
For a copy of the password, please e-mail me (firstname.lastname@example.org) using your work e-mail. Include your name, e-mail address, and link to your school website for confirmation purposes.
"assessss" (v3) is an Excel spreadsheet updated to add greater functionality. It is 5MB.
DEFINITION OF NORMALIZED GAIN: Physics education researchers cite the FCI normalized gain, <g>, in addition to pretest and posttest scores, because it enables courses at many different levels, from conceptual to calculus-based college physics, to be directly compared as to effectiveness of instruction.
The normalized gain is the actual gain divided by the possible gain. Specifically, let <. . . > mean an average over all students in a course. Define <%gain> as the average actual gain (<%post> - <%pre>) for a course, & define average NORMALIZED gain for a course as:
<g> = (<%post> - <%pre>)/(100% - <% pre>) = <%gain> /(maximum possible %gain)
UPDATES TO THIS assessssv3.xls SPREADSHEET:
David Dellwo's paper on "Re-assessing Hake's Gain" was presented at the Washington D.C. Winter 2010 AAPT meeting. In this paper David describes a sensible argument on how to improve upon Richard Hake’s discussion of normalized gain. Dr. Dellwo reasons that a true "gain" would be one in which the student had pretested a problem incorrectly and posted the problem correctly. Likewise, a true "loss" would be a correctly pretested problem but incorrectly posttested problem. This allows for a true gain (Dellwo gain or "Dgain" - my definition, not his) and true loss (Dellwo loss or "Dloss"). Lastly, the student correct pre and posttest problems are defined as retention "R%". Since these were easy manipulations of my spreadsheet, I have updated the analysis columns to include not only the traditional "Hake" normalized gain, "Hgain", but these new calculations as well.
The original papers describing the above in detail are at:
PRIOR ASSESSSS VERSION 2 SPREADSHEET INFORMATION:
The assessssv2.xls spreadsheet has the ability to provide individual and entire class item analysis, especially helpful after pre-testing to determine instructional focus areas. assessssv2 is preset for the force concept inventory, test for understanding graphs in kinematics, mechanics baseline test and has additional answer keys for energy concept inventory and the conceptual survey of electricity and magnetism.
You can choose to enter data either alphabetically (A,B,C...) or numerically (1,2,3...) and either pre/post or post test only options. Numeric data is more easily input manually. Alphabetic input is useful if you have access to automated scoring hardware that can export scanned data to spreadsheets.
A fourth worksheet, "Test 4" is provided for an additional assessment, backup, etc. The four worksheets are directly linked to the roster and will automatically transfer individual and class averages from the four worksheets. Please note that you can write over the three preset assessments to add any test of your choosing.
Assessssv2 will open with three windows, the default being the roster and the FCI posttest (top window) and pretest (bottom window) with sample dummy data. To change to another data set you must select the proper "workbook" found at the bottom menu bar of the right hand windows.
The item analyses are found to the right of the post-test column. Because up to 100 responses can be inserted, you must scroll over to the items analysis. In the case of the mechanics baseline test the bottom panel is set up to automatically view the post-test items analysis. Unfortunately the superfluous columns cannot be deleted without affecting the column references - the price of flexibility.
Every column of the roster worksheet is linked to all other worksheets within assessssv2. To add students to the roster, first cut and paste student names and scores to the "scratch" workbook. Use the sort function to alphabetize names and copy the names and scores to the roster. Remember to adhere strictly to this list, adding dummy data as needed to fill in for missed scores or students dropping a class in order to be sure grades correlate to students. If you sort only the names on the roster page, you will scramble all the remaining links. i.e. student B may receive the scores of student A etc..
Again, for a copy of the password, please use your work e-mail address to e-mail me your name, e-mail address, and link to your school website for confirmation purposes. (If you don’t hear from me within two days, please email again. If your school is particularly restrictive of outside e-mail, please say so, and include your home e-mail address.)
Kind regards, Jamie
James Vesenka, Professor of Physics
Department of Chemistry and Physics
University of New England
11 Hills Beach Road
Biddeford, ME 04005