bright
bright
bright
bright
bright
tl
 
tr
lunder
Excel Spreadsheets for Classical Test Analysis
This site designed and maintained by
Prof. Glenn Fulcher

@languagetesting.info

runder
lcunder
rcunder

 
Right Click and Select "Save As...."
     
 
The Excel spreadsheets on this page have all been written by William Bonk (University of Colorado), who kindly donated them to the Language Testing Resources website when he was no longer able to distribute them through his own web page. The five spreadsheets calculate the basic statistics developed in classical test analysis for closed response items such as multiple choice. These include distractor analysis, item facility, a discrimination index, reliability, and descriptive statistics (mean, standard deviation, and standard error of measurement). The spreadsheet for Cronbach's alpha also handles partial credit data.
When you first open a file make sure that the spreadsheet is maximised in Excel so you can see all the scroll bars and tabs. In order to do this, click on the expand button in the top right as shown in this illustration.
There are additional instructions in portable document format for Cronbach's alpha and the Score Converter. Download here.


Score Converter

Description: This spreadsheet converts scores to a set of 0's and 1's. On the data input sheet you enter the key in the top row as 'a', 'b', 'c' or 'd' (or another number of options if required). Each subsequent row is a case, in which you enter the response of that person for each item. When you click on the 0's and 1's tab and scroll to the top of the page you will have a set of 0's and 1's that you can cut and paste into other spreadsheets for analysis.
Distractor Analysis

Description: Analysis of the number of responses to each of the options in multiple choice items (the key and the distractors) can be used to inform the revision of items to maximise item variance. In this spreadsheet the items in the test are listed across the top row, and the cases are in the left column. For each case (test taker) you enter the answer they gave to the item as 'a', 'b', 'c' or 'd'. When you scroll down the spreadsheet the number of a's, b's etc. are summed, and the percentage for each response is also given.
Reading: Fulcher, G. (2010). Practical Language Testing. London: Hodder Education, pp. 172 - 173; 191. Fulcher, G. & Davidson, F. (2007). Language Testing and Assessment: An Advanced Resource Book. London and New York: Routledge, pp. 326 - 327.
Item Facility and Point Biserial Correlation

Description: Item Facility tells us how difficult an item is for the intended population. The Point Biserial Correlation is a measure of discrimination. In this spreadsheet you paste item responses as 0's and 1's into the cells, with items along the top row and cases down the left hand column. When you scroll down the page the spreadsheet calculates the item facility for each item, and the point biserial correlation (Rpbi: a correlation between a dichotomous and a continuous variables).
Reading: Fulcher, G. (2010). Practical Language Testing. London: Hodder Education, pp. 182 - 185. Fulcher, G. & Davidson, F. (2007). Language Testing and Assessment: An Advanced Resource Book. London and New York: Routledge, pp. 102 - 104.
Web Feature: These statistics are primarily for use in the analysis of multiple choice tests. There are many assumptions underlying the use of these statistics, some of which I discuss in this feature and podcast.
Cronbach's Alpha

Description: Cronbach's alpha is the most commonly used statistic for reporting test reliability based on item variances. In the data input tab you cut and paste the scores for each item as 0's and 1's for each case. When finished, click on the results tab. The spreadsheet calculates the test mean, the standard deviation, the standard error of measurement, and Cronbach's alpha. This spreadsheet can also handle partial credit data on a scale of up to 7 levels (see additional instructions above).
Reading: Fulcher, G. (2010). Practical Language Testing. London: Hodder Education, pp. 51 - 54, which also explains the use of alpha for partial credit scoring. Fulcher, G. & Davidson, F. (2007). Language Testing and Assessment: An Advanced Resource Book. London and New York: Routledge, pp. 106 - 108. Bachman, L. F. (2004). Statistical Analyses for Language Assessment. Cambridge: Cambridge University Press, 163 - 171, which outlines other procedures for calculating reliability based on item variances.
Kuder-Richardson 21

Description: KR21 is a short-cut method to estimate alpha. For KR21 all you need is the mean score on the test, the standard deviation, and the number of items (k). Alpha should be calculated whenever possible, but KR21 can provide reasonable reliability estimates for tests with dichotomous items.
Reading: Fulcher, G. & Davidson, F. (2007). Language Testing and Assessment: An Advanced Resource Book. London and New York: Routledge, pp. 106 - 107.