|
|
| |
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). The spreadsheet was updated in December 2017 and now allows the user to input up to 100 items.
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.
|
|
|
| |
|
|
|
|
| |
| |
|
| |
|
|
| |
|
|
|
