Student’s t-test for equal means

Home > String Operations, Uncategorized, Useful Procedures > Student’s t-test for equal means

October 29th, 2008 Leave a comment Go to comments

If you do not wish to enter complex formula in Excel and you already have calculated average, count, and variance for your samples(Tip:use a PivotTable), then you can use these user-defined functions to calculate the t-test stat value and degrees of freedom required to do hypothesis testing. Both the functions provide an optional argument for the assumption of equal variances; by default it is set to false.

Here’s the code for t-test:
‘—————————————————————————————
‘ Procedure : TTESTM
‘ DateTime  : 10/29/2008 08:35
‘ Author    :
‘ Purpose   : To get the t-stat value when comparing two means with an optional input
‘               for equal or unequal variances
‘               by default the function assumes that the user is comparing means with unequal variances
‘—————————————————————————————
‘
Public Function TTESTM(davg1 As Double, dcnt1 As Double, dvar1 As Double, davg2 As Double, dcnt2 As Double, dvar2 As Double, Optional blnEqVar As Boolean = False) As Double

On Error GoTo TTESTM_Error
Dim dResult As Double, dNumer As Double, dDenon As Double, dPooledVar As Double

dNumer = davg1 – davg2

‘if equal variances are not assumed
‘http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
If Not blnEqVar Then
    dDenon = Sqr((dvar1 ^ 2 / dcnt1) + (dvar2 ^ 2 / dcnt2))
Else
‘if equal variances are assumed, then calculated the pooled variance
    dPooledVar = Sqr((((dcnt1 – 1) * dvar1 ^ 2) + ((dcnt2 – 1) * dvar2 ^ 2)) / (dcnt1 + dcnt2 – 2))
    dDenon = dPooledVar * Sqr((1 / dcnt1) + (1 / dcnt2))
End If

dResult = dNumer / dDenon

TTESTM = dResult
Exit Function

TTESTM_Error:
TTESTM = Null

End Function

Here’s the code for the degrees of freedom:
‘—————————————————————————————
‘ Procedure : DOFTTESTM
‘ DateTime  : 10/29/2008 08:50
‘ Author    :
‘ Purpose   : To get the degrees of freedom for the t-test when comparing two means with an optional input
‘               for equal or unequal variances
‘               by default the function assumes that the user is comparing means with unequal variances
‘—————————————————————————————
‘
Public Function DOFTTESTM(dcnt1 As Double, dvar1 As Double, dcnt2 As Double, dvar2 As Double, Optional blnEqVar As Boolean = False) As Double

Dim dResult As Double, dNumer As Double, dDenon As Double

On Error GoTo DOFTTESTM_Error
‘if equal variances are not assumed, then use a complicated formula to compute degrees of freedom
‘http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
    If Not blnEqVar Then
        dNumer = ((dvar1 ^ 2 / dcnt1) + (dvar2 ^ 2 / dcnt2)) ^ 2
        dDenon = ((dvar1 ^ 2 / dcnt1) ^ 2 / (dcnt1 – 1)) + ((dvar2 ^ 2 / dcnt2) ^ 2 / (dcnt2 – 1))
        dResult = dNumer / dDenon
    Else
        dResult = dcnt1 + dcnt2 – 2
    End If

DOFTTESTM = dResult