座右铭:
只做有益人类的事
不做有害人类的事
|
|
Enhanced Chi-Square Test Function in Excel
Zhanshan Dong
In Excel, there is one handy function called CHITEST. By using it, you can easily test for independence. CHITEST returns the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom. You can use Chi Square tests to determine whether hypothesized results are verified by an experiment. CHITEST returns the probability for a Chi-Square statistic and degrees of freedom, df, where df = (r - 1)(c - 1). (This paragraph is adopted from Excel Help Document).
But, when the degree of freedom is equal to 1, it is recommended to correct the X2 statistic by using Yates correction. To do this, the statistic must be calculated manually. To automate the procedure, a enhanced function was written (see the source code list below). This function does the correction whenever the degree of freedom is qual to 1, otherwise it calls the Excel worksheet function ChiTEST to return the p-value.
' This is a Chi-Square Test function
' When degree of freedom is one, it will execute the Yates Correction
' Otherwise, it calls the worksheet function (ChiTest) to do the normal Chi-Square test
Public Function CHITESTYC(ObsRange As Object, TheoRange As Object) As Double
Dim nCat As Integer
nCat = UBound(ObsRange(), 1)
If nCat = 2 Then
ChiSquare = 0
For i = 1 To nCat
ChiSquare = ChiSquare + (Abs(ObsRange(i) - TheoRange(i)) - 0.5) ^ 2 / TheoRange(i)
Next i
p_value = WorksheetFunction.ChiDist(ChiSquare, nCat - 1)
CHITESTYC = p_value
Else
CHITESTYC = WorksheetFunction.ChiTest(ObsRange, TheoTange)
End If
End Function
|
|
|
