Psychometrics in Excel: Scale Scoring, Reliability & Item Analysis

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Recode the two negatively worded items of a fictional wellbeing questionnaire, see how reversing flips the correlation sign, and count straight-liners.
• Score the DWS-6 for every respondent, apply a 'at least 5 of 6 items' rule, and prorate the total for a respondent who skipped one item.
• Turn the DWS-6 totals into z-scores and T-scores so that each respondent's position in the group can be read at a glance.
• Calculate Cronbach's alpha for the DWS-6 step by step, from the six item variances and the variance of the total, and predict alpha for a longer scale.
• Correlate every DWS-6 item with the scale total, correct for the item's own part in the total, flag weak items and chart the result.
• Recalculate alpha six times, each time without one item, and find the item whose removal raises the reliability of the DWS-6.
• Correlate two DWS-6 sittings four weeks apart, turn the reliability into a standard error of measurement, put a 95% band around a score and find who changed by more than measurement error.
• Build a norm table from a fictional norm sample: cumulative frequencies, percentile ranks per score band, a lookup for one respondent, a normalised T-score and the cumulative curve.