how to perform Ttest using Excel VBA on a large dataset

Hello amalchacko,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

Hi amalchako,

We need to be able to define the two ranges specifically - have you criterion for such?

Hi amalchako,

Try this:

Sub amalchako(): Dim WF As Object, TT As Single, R1 As Range, R2 As Range, Y As Long
Dim Mvmt As String, TP As String, LastRow As Long, h As Long, k As Long, r As Long
                Set WF = WorksheetFunction
        LastRow = Range("H" & Rows.Count).End(xlUp).row
For r = 2 To LastRow: Mvmt = Cells(r, 2): TP = Cells(r, 6): Y = Cells(r, 3)
            h = 0: Do: h = h + 1: Loop Until Cells(r + h, 3) <> Y
            Set R1 = Range("H" & r & ":H" & r + h - 1)
            k = 0: Do: k = k + 1: Loop Until Cells(r + h + k, 2) <> Mvmt
            Set R2 = Range("H" & r + h & ":H" & r + h + k - 1)
            If WF.CountA(R2) = 0 Then
            Cells(r, 10) = "N/A": Cells(r, 11) = Mvmt
            Exit Sub: End If
            Range(R1, R2).BorderAround Weight:=xlMedium

TT = WF.TTest(R1, R2, 1, 3): Cells(r, 10) = TT: Cells(r, 11) = Mvmt: r = r + h + k - 1

Next r
End Sub

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name