How to score a validated questionnaire from a raw survey export

Start from the scoring manual, not the spreadsheet

Every validated instrument ships with a scoring manual or a scoring section in its development paper. The PHQ-9, for example, sums nine items scored 0 to 3 and interprets the total against published cut points. PROMIS short forms convert a raw sum into a T-score through a lookup table. The manual is the source of truth for what a score means, so the first task is to find it and read the scoring section before touching the data.

Survey platforms rarely export data in the instrument's native format. Qualtrics, REDCap, and Google Forms label columns by their own question IDs, store response text instead of numeric codes, and sometimes split a matrix question into one column per item. None of that is wrong, but none of it is the instrument yet.

Map every column to an item

Build a small mapping table with one row per export column: the platform's column name, the instrument item number, the response options as they appear in the export, and the numeric code the manual assigns to each option. This table is the artifact reviewers ask for when they question a score, and it is what lets a second analyst reproduce your result.

Watch for three common mismatches. Response labels that differ slightly from the manual, such as "Several days" exported as "2-6 days". Items that were dropped or reordered when the survey was built. And items answered on a different scale than the instrument specifies, which usually means the item cannot be scored as published and the limitation belongs in the methods.

Reverse-code exactly the items the manual names

Many instruments include items worded in the opposite direction so that agreement does not always mean more of the construct. The manual lists which items to reverse and how. For a 1 to 5 scale, a reversed item is recoded as 6 minus the response. Reverse only those items, and record the list. Reversing an extra item or missing one quietly changes every downstream score while leaving the data looking plausible.

A useful check after recoding is the correlation between each item and the rest of its subscale. After correct reverse coding, every item should correlate positively with its subscale total. A negative item-total correlation almost always points to a coding error.

Apply the missing-item rule the instrument specifies

Instruments differ on what to do when a respondent skips an item. Some allow prorating when at least a set share of items are answered, for example computing the mean of answered items and multiplying by the item count. Others treat any missing item as a missing total. PROMIS provides a response-pattern method for partial short forms. Use the rule the instrument publishes, and report how many respondents it affected.

Do not fill skipped items with the scale midpoint or zero unless the manual says so. Both choices bias scores toward the middle and are difficult to defend under review.

Compute scores and keep the record

Compute subscale scores first, then totals, in code rather than by hand in a spreadsheet. Save the code, the mapping table, the list of reversed items, the missing-item rule, and the counts of respondents affected at each step. That bundle becomes one paragraph in the methods section and a supplementary file if a journal asks for it.

The most reliable final check is to score two or three respondents by hand from the manual and confirm the code produces the same numbers.

• Mapping table: export column, item number, response options, numeric codes.
• Reverse-coded item list with the recoding formula.
• Missing-item rule and the number of respondents it affected.
• Scoring code and a hand-scored spot check.