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 ' davg1 is the mean of first sample ' dcnt1 is the sample size of sample 1 ' dvar1 is the variance of first samaple ' davg2 is the mean of second sample ' dcnt2 is the sample size of sample 2 ' dvar2 is the variance of second samaple ' blnEqVar set TRUE to assume equal variance for the test '--------------------------------------------------------------------------------------- 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 |
Code for 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 ' dcnt1 is the sample size of sample 1 ' dvar1 is the variance of first samaple ' dcnt2 is the sample size of sample 2 ' dvar2 is the variance of second samaple ' blnEqVar set TRUE to assume equal variance for the test '--------------------------------------------------------------------------------------- ' 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 |