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
Exit Function
DOFTTESTM_Error:
DOFTTESTM = Null
End Function
Recent Comments