Programming and Application(编程与应用)

Content(目录)

Linux

MySQL
Office

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

Post comments(留言)

由Google提供