Hypothesis Testing — 2 Sample t Test — Create your quick working guide

Hypothesis testing is a very important element in many areas of work which includes studies and education also. Many think that there is a statistical software needed to perform hypothesis testing — but the good news is that it can be done very easily using MS Excel formula . In this article I will explain how we can perform 2 Sample t Test using MS Excel. Before we proceed, if you have not read my article Hypothesis Testing — One Sample z Test — How to create your quick workbook using MS Excel , request you to spend 5 min to read this. You can also visit my Facebook Page https://www.facebook.com/FBTrainBrain for such interesting articles.

The 2 Sample t Test is used to test whether the unknown population means of two separate groups are equal or not.

In this article we will learn on how to perform the t test in the Equal Sample Size Equal Variance scenario.

We will learn about both the following ways of performing the test:

• p-value approach
• Critical Value approach

Let us form the problem statement and see the datasets:

Mean of Population 1 (not known) : m1

Mean of Population 2 (not known): m2

Null Hypothesis (Ho) : m1 = m2

Alternate Hypothesis(Ha) : m1 Not Equal To m2

Based on the Null and Alternate Hypothesis , this is a Two Tail Test

Confidence Level: 0.05

Now let us see the datasets:

Sample Data Sets

For each of the sample data above, let us first calculate : Mean, Standard Deviation, Variance and Sample Size using simple excel formulae as below:

Image 1

p-value approach:

First we have to calculate t Statistic value. Mathematically t Statistic is defined as :

(Sample1 Mean — Sample2 Mean) / [SquareRoot((Sample1 Standard Deviation² + Sample2 Standard Deviation²) / Sample Size)]

Using the above formula, let us calculate the t Statistic in our case. Keep referring to Image1

t Statistic = (G4-G12) / SQRT(((G5 ^ 2 )+(G13 ^ 2))/G7) = -0.66183 [assume that the t Statistic value of -0.66183 is kept in cell G16]

Since this is a Two Tail Test, hence formula for calculating p-value is:

TDIST( absolute value of <t statistic value>,<degrees of freedom>,<tail>)

p-value=TDIST(ABS(G16),G7+G15–2,2)=0.5174

TDIST is an excel function for t Distribution

ABS is an excel function for finding the absolute value

Conclusion based on p-value : Since the p-value (0.5174) is more than the Significance Level(0.05) , we fail to reject Null Hypothesis

NOTE:

For Left Tail Test:

• If t Statistic >0 then p-value = 1 — TDIST(<t statistic value>,<degrees of freedom>,<tail>)
• If t Statistic <0 then p-value = 1-(1 — TDIST(absolute value of <t statistic value>,<degrees of freedom>,<tail>))

For Right Tail Test:

• If t Statistic >0 then p-value = TDIST(<t statistic value>,<degrees of freedom>,<tail>)
• If t Statistic <0 then p-value = 1 — TDIST(absolute value of <t statistic value>,<degrees of freedom>,<tail>)

Critical Value Approach

We already know the t Statistic Value which is -0.66183( and this value is kept at G16 cell of the excel sheet)

For critical value approach, we first need to calculate the Upper Critical Value and Lower Critical Value:

Upper Critical Value = TINV( 2*<Significance Level>,<degrees of freedom>) = TINV(2*0.05,G7+G15–2)= 1.7458

Lower Critical Value = -TINV( 2*<Significance Level>,<degrees of freedom>) = -TINV(2*0.05,G7+G15–2)= -1.7458

Conclusion based on Critical Value : Since the t Statistic (-0.66183) is within the Critical Value Intervals [-1.7458, 1.7458] , we fail to reject Null Hypothesis

IMPORTANT NOTE:

• For Left Tail Test t Critical value is calculated as -TINV( 2*<Significance Level>,<degrees of freedom>)
• For Right Tail Test t Critical value is calculated as TINV( 2*<Significance Level>,<degrees of freedom>)

Thanks for your interest in reading through this article. Watch this space for many more such handy techniques .