Can't calculate paired T-value in Visual Basic
May 16, 2008 2:53 AM Subscribe
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
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
Response by poster: 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, 2008
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, 2008
Sorry, I don't know anything about Visual Basic.
posted by a robot made out of meat at 5:37 AM on May 16, 2008
posted by a robot made out of meat at 5:37 AM on May 16, 2008
Response by poster: 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, 2008
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, 2008
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, 2008
posted by flabdablet at 8:32 AM on May 16, 2008
Best answer: I just checked that by opening a new spreadsheet and putting these subroutines in it:
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, 2008
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, 2008
This thread is closed to new comments.
posted by a robot made out of meat at 4:48 AM on May 16, 2008