Missing Data
One approach to dealing with missing data is to use the following formula for Cronbach’s alpha:
We can calculate Cronbach’s alpha for Example 1 of Cronbach’s Alpha Basic Concepts as shown in Figure 1 where range N4:X14 contains the array formula =COV(B4:L15).
Figure 1 – Alternative calculation of Cronbach’s alpha
When there is missing data, instead of performing listwise deletion on the original data, we can perform pairwise deletion when calculating the covariance matrix. Thus, we can calculate Cronbach’s alpha on the same data with some missing values as shown in Figure 2.
This time we use =COV(B4:L15,FALSE) to calculate the values in range N4:X14, obtaining a Cronbach’s alpha of .77729.
Figure 2 – Cronbach’s alpha with missing data
Not Applicable Responses
In some questionnaires, in addition to a dichotomous response such as “yes” or “no”, a response such as “not applicable”, “no opinion” or “undecided” may be used. Such a response is a real response and so should not be treated as missing data. A coding such as yes = 2, N/A = 1 and no = 0 is also usually not appropriate since this implies an ordering which may not be appropriate.
A better approach is to calculate Cronbach’s alpha in the usual way, ignoring the N/A responses, but to scale up the sums in each row. This approach is shown in Figure 3.
Figure 3 – Cronbach’s alpha with N/A entries
The calculations are identical to that shown in Figure 1 of Cronbach’s Alpha Basic Concepts except for column M. The values in this column are calculated by placing the formula =SUM(B4:L4)*$B$19/COUNT(B4:L4) in cell M4, highlighting the range M4:M15 and pressing Ctrl-D. The value in cell B21 is calculated by the formula =VAR.P(M4:M15).
Reference
Arifin, W. R. (2018) Calculating the Cronbach’s alpha coefficients for measurement scales with “not applicable” option
Available through Researchgate
Matysova, K. (2019) Evaluation of modern missing data handling methods for coefficient alpha
https://digitalcommons.unl.edu/cgi/viewcontent.cgi?article=1353&context=cehsdiss