Cronbach’s Alpha in Excel VBA
by Nick V. Flor • March 8, 2016 • @ProfessorF
Here’s the bulk of my implementation of Cronbach’s Alpha in Excel VBA. If you’re having problems with the formula on the internet, hopefully this piece of code explains how to translate the math into code. This is the formula:
Function fCron(Y) Dim mRows, nCols, i, j Dim X, V, sumVY, VX
getNumRowsCols Y, mRows, nCols ' Calculate X vector=SUM Y-Rows X = createMatrix(mRows, 1) For i = 0 To (mRows - 1) For j = 0 To (nCols - 1) X(i, 0) = X(i, 0) + Y(i, j) Next Next VX = fVar(X, 0) ' Variance of X ' Calculate Variances of Ys VY = createMatrix(1, nCols) sumVY = 0 For j = 0 To (nCols - 1) VY(0, j) = fVar(Y, j) ' for debugging sumVY = sumVY + VY(0, j) ' Sum of Y Variances Next k = nCols fCron = (k / (k - 1)) * (1 - (sumVY / VX)) End Function
If you need help implementing any of these functions or understanding the code, I’m happy to answer your questions.
In my opinion, Excel cannot be beat for Exploratory Data Analysis of social media postings. It’s just missing a couple of key functions like factor extractions, factor rotation, reliability indicators, clustering, sentiment analysis, and social networking graphs.