Can't calculate paired T-value in Visual Basic

May 16, 2008 2:53 AM

Visual Basic and Excel: How do I replicate the results of excel’s internal function for paired dual tailed t-test in Visual Basic. I tried and failed, where did I go wrong?

I’m admittedly a novice at Visual basic, but I succeeded in reproducing the t-test for Two-sample equal variance (homoscedastic) in Visual Basic. TTEST(array1,array2,2,2). I’m at a loss why I can’t be able to do the same for the paired t-test.

One possible explanation could be that I’m not using the same formula as excel does, but the formula that excel uses for the TTEST(array1,array2,2,1) is nowhere to be found. I used the formula in my statistics book that agreed with the one called Dependent t-test from the Wikipedia page for student’s T-test .
Is that the one excel uses for its TTEST(array1,array2,2,1) function?

Where else could I have gone wrong?

This is the code I made (again, I’m a novice, be gentle):

'The input consist of the area the data is located in, the columns containing the data we will use and the number of rows the area consist of
Function t_test1(area, column1, column2, NoOfRows)

Dim i, X_SubD, X_SubD2, Sample_stdev, T_TestValue

'Will eventually become the average difference
X_SubD = 0

'Will eventually become the sum of squared difference
X_SubD2 = 0
Sample_stdev = 0

For i = 1 To NoOfRows

X_SubD = X_SubD + (area(i, column1) - area(i, column2))

X_SubD2 = X_SubD2 + (area(i, column1) - area(i, column2)) ^ 2

Next i

'Calculate the average
X_SubD = X_SubD / NoOfRows

'Calculate the sample standard deviation
Sample_stdev = ((X_SubD2 - NoOfRows * X_SubD ^ 2) / (NoOfRows - 1)) ^ 0.5

'Calculate T-value
T_TestValue = X_SubD / (Sample_stdev / (NoOfRows ^ 0.5))

'Look up the significance and return the value
If T_TestValue < 0 Then
t_test1 = Application.WorksheetFunction.TDist(-T_TestValue, 2 * NoOfRows - 1, 2)
Else
t_test1 = Application.WorksheetFunction.TDist(T_TestValue, 2 * NoOfRows - 1, 2)
End If

End Function

posted by JeNeSaisQuoi to computers & internet (8 comments total)

It looks like the TLookup is 2N-1 df, but it should be N-1.
posted by a robot made out of meat at 4:48 AM on May 16

Thanks a million. That's it!

Might I milk your genius for just a bit more brilliance?

Is there any way I could dispense of the NoOfRows parameter and calculate that one in the VB function itself? I.e., for example, could I calculate within the function that the number of rows of the “area” parameter was 3 if the two dimensional array I had selected as “area” were A1:B3?
posted by JeNeSaisQuoi at 5:19 AM on May 16

Sorry, I don't know anything about Visual Basic.
posted by a robot made out of meat at 5:37 AM on May 16

Here you go.
posted by flabdablet at 8:01 AM on May 16

flabdablet , thx but I don't get it to work. The array comes from a range of cells from a sheet. I think, perhaps, that's why it doesn't work.

For example I tried this function:

Function CountRow(area)
Dim x, i


For x = LBound(area) To UBound(area)
i = i + 1
Next x

CountRow = i

End Function

But if i select an area it doesn't work. I.e. =CountRow(A1:A3) returns an error. Did i do something wrong?
posted by JeNeSaisQuoi at 8:17 AM on May 16

If your array is two-dimensional, which it probably will be for an arbitrarily selected spreadsheet area, then you'll need to tell LBound and UBound which dimension to use. Try evaluating UBound(area, 1) and UBound(area, 2).
posted by flabdablet at 8:32 AM on May 16

I just checked that by opening a new spreadsheet and putting these subroutines in it:

Sub test()
testbounds Range("b2:b7").Value
testbounds Range("b2:c7").Value
testbounds Range("b7:c7").Value
End Sub

Sub testbounds(a)
MsgBox LBound(a, 1) & ".." & UBound(a, 1) & ", " & LBound(a, 2) & ".." & UBound(a, 2)
End Sub

When I run ThisWorkbook.test, the three message boxes display

1..6, 1..1
1..6, 1..2
1..1, 1..2
posted by flabdablet at 9:30 AM on May 16

You made my day, thx!
posted by JeNeSaisQuoi at 10:11 AM on May 16