Regents Exam Item Analysis Templates
Templates for other June 2008 exams coming soon
These spreadsheets compute scaled scores and item analyses for
various Regents exams. They make grading faster, reduce errors, and
yield instructionally useful data, besides organizing sometimes
overwhelming quantities of data and making life easier for your grade
czar.
The templates are basically similar, but each one has the
appropriate number of questions, multiple choice key, and conversion
chart. (With some basic Excel savvy, you can easily modify a template
for use with a different administration, a different subject, or even
for a non-Regents exam. See Modifying the
Templates below for details.)
I've also posted templates for past exams, which can make the use of
those exams for diagnosis, practice, and test preparation more
informative and so more effective.
Each template has four parts (one of which is hidden). The columns
mentioned here are those for the Math A Regents exam templates (other
exams have the same parts, but since they have different numbers of
questions, the columns are different).
- Multiple choice answers (columns F to AI)
- Multiple choice points (columns AJ to BM) (hidden)
- Free response points (columns BP to BX)
- Totals and analysis (columns BY to CC and the rows at the bottom)
Basically, you enter students' responses (multiple choice answers and
free response points) and the sheet will give you the students' scaled
scores and item analysis information.
(The specific columns may vary for different templates. The column
names used here are for the Math A exam, which has 30 multiple-choice
questions and nine short-answer questions.)
- Download the appropriate template. (If you click the link, be sure
to save it, not just open it. In most browsers, you can also
right-click the link and select "Save".)
- Each student's info goes in a different row of the sheet. (You can add more rows by inserting blank rows, then copying down. You can also delete unused student rows.)
- Enter or paste each student's ID number (if desired) and name in
columns A-C. (The ID number can help when uploading scores into a
gradebook or transcript system.) You can modify these or add columns
without affecting the rest of the sheet. The hidden columns (D-E)
might be useful to flag students by period, grade level, teacher, etc.
- Enter each student's multiple-choice answers in columns F-AI. The
conditional formatting will flag answers as correct (green, bold) or incorrect (red).
- Enter each student's free-response points in columns BP-BX.
- The student's raw and scaled scores will appear in the rightmost columns.
- Item analysis data will appear at the bottom of each row.
If there is a topic for each question (below each item's analysis) it
is my own. If you find any errors, please let me know so I can fix the
posted file.
Note that this item analysis sheet does not yet incorporate the
conversion chart for scaled scores. The exam and scoring key do
not appear to be online yet. The conversion chart should be posted on
June 26. (A new version of the item analysis spreadsheet will be
posted as soon as possible after the conversion chart is released.)
Download past exams (including translated
editions), scoring keys, and conversion charts from the NYSED
website.
Math A, January 2008
Math A, August 2007
Math A, June 2007
Math A, January 2007
Math A, August 2006
Math A, June 2006
Math A, January 2006
Download past exams, scoring keys, and conversion charts from the
NYSED
website.
Math B, January 2008
Math B, June 2007
Math B, January 2007
Math B, June 2006
I may have templates for a few other exams as well, but I haven't
checked these as carefully as the math exams' analysis templates, so
I'm only posting a few. Please do let me know if there are any
errors. You can download past exams, scoring keys, and conversion
charts from the NYSED
website, and it would be a good idea to check these over before
you use them.
Living Environment, June 2007
US History, June 2007
Earth Science, June 2007
English Language Arts, June 2007
To edit an existing template for a different administration of the same exam:
- If you just want to tabulate answers, get an item-level analysis, and
raw scores:
Edit the "Key" (row 2 on the "Scores" tab) for all the
correct multiple-choice answers. Then it's ready for entry.
- If you also want scaled scores, modify the key, AND:
Edit the scale on the "Conversion" tab. Conversion charts for past
exams are available at the NYSED website (see links above).
- For math exams, if you also want results by learning standard,
modify the key, AND:
Edit the "problem numbers" on the "Map" tab. You can find this map on
the last page of the scoring key for each exam. Keys for past exams
are also available at the NYSED website (see links above).
You can also edit an existing template for a different exam. This may
require some Excel skill. (I'm happy to help out with this if I have
time.)
Page last modified 19 June 2008.