T critical one tail excel

In this document we will discuss 2 – sample T- hypothesis testing and confidence

intervals that uses a mean’s and unequal unknown Sample SD’s.

This PDF discusses T-Critical Value and you are assuming unequal variances and

discussing a sample mean and sample SD.

There are still 3 different hypothesis scenarios with a 2 – Sample T Hypothesis

Test.

Lower Tail Test (1 tail):

Ho: �̅�1 − �̅�2 = 0

Ha: �̅�1 − �̅�2 < 0

Upper Tailed Test (1 tail):

Ho: �̅�1 − �̅�2 = 0

Ha: �̅�1 − �̅�2 > 0

Two Tailed Test:

Ho: �̅�1 − �̅�2 = 0

Ha: �̅�1 − �̅�2 ≠ 0

The hypothesized value is 0 and the same key words apply from a 1 – sample

hypothesis test to determine which scenario to use. 𝜇1 − 𝜇2 is the difference

between the average in the first sample and the average in the second sample.

The T – Test Statistic = �̅�1− �̅�2−0

√ 𝑆𝐷1

2

𝑛1 +

𝑆𝐷2 2

𝑛2

Where SD is the sample standard deviation, 𝜇1 𝑎𝑛𝑑 𝜇2 are averages and n1 and n2 are the sample sizes.

We can use =T.DIST, =T.DIST.RT and =T.DIST.2T to find the p-values. These should

look familiar from the discussion forum.

Example:

Suppose you wish to compare a new method of teaching reading to “slower

learners” with the current standard method. You decide to base your comparison

on the results of a reading test given at the end of a learning period of 6 months.

A random sample of 23 “slower learners”, 11 are taught by the new method and

12 are taught by the standard method. All 23 children are taught by qualified

instructors under similar conditions for 6 months. Does the new reading method

increase test scores when compared to the standard reading method? Use Alpha

= .05.

New Method

Standard Method

80 79

76 73

70 72 80 62

79 76 66 68

85 70 71 86

81 75

76 68 75 73

66

First step is to state the hypothesis scenario. Because the key word says increase

this means it is an upper tailed test.

Ho: �̅�1 − �̅�2 = 0

Ha:�̅�1 − �̅�2 > 0

Before we start calculating anything by hand and because we are given the raw

data set, we can actually run this hypothesis test in Excel. And since you installed

the Data Analysis Toolpak it is easy to do. First you need to input this Raw Data

into Excel.

Then go to Data -> Data Analysis -> and scroll to where it says t-Test: Two-Sample

Assuming Unequal Variances and click OK

Under Input:

Variable 1 Range: you will highlight the New Method column and make sure you

include the top row where the Label is located.

Variable 2 Range: you will highlight the Standard Method column and make sure

you include the top row where the Label is located.

Hypothesize Mean Difference: 0

Check the “Labels” box because we did include the first row of labels. For Alpha

out 0.05 but this can be change depending on what significance level you use.

Then make sure the bubble for New Workbook Ply: highlight and click OK. It

should look similar to the screenshot below.

Once you click OK in a new Worksheet this should populate.

t-Test: Two-Sample Assuming Unequal Variances

New Method Standard Method

Mean 76.27272727 72.33333333 Variance 30.81818182 40.24242424

Observations 11 12 Hypothesized Mean Difference 0

df 21

t Stat 1.587847328

P(T<=t) one-tail 0.063632175

t Critical one-tail 1.720742903

P(T<=t) two-tail 0.12726435

t Critical two-tail 2.079613845

Here we have all the values we need to state a conclusion.

We see the T - Test Statistic = 1.587847 and because we ran an upper tailed test

the p-value = .063632.

p -value = .063632 > .05. This p-value is greater than .05 which means we Do Not

Reject Ho. No, there is not statistical evidence that the new reading method

increase test scores when compared to the standard reading method.

If we were running a 2-tailed test, we are given the p-value which is .12727. T-

Test Statistic is the same and so is the conclusion for a 2-tailed test.

Using Excel to run a hypothesis test when we are given the Raw Data is very

convenient. But if we aren’t given the Raw Data and we are given the averages

and unknown sample SD’s we will need to compute the T-Test Stat by hand and

then use the Excel function to find the p-value.

To find the T-Test Stat we will use this equation and plug in what we know. You

should know by now how to calculate the average and SD using Excel. Which is

what I did here

T – Test Statistic = �̅�1− �̅�2−0

√ 𝑆𝐷1

2

𝑛1 +

𝑆𝐷2 2

𝑛2

T – Test Statistic = 76.2727− 72.3333−0

√5.551413 2

11 +

6.3436922

12

T – Test Statistic = 3.9394

√2.8016533+3.3535356

T – Test Statistic = 3.9394

√6.1551889

T – Test Statistic = 3.9394

2.48096

T – Test Statistic = 1.58785

When we calculate the Test Stat by hand using algebra we get the same value.

Next, we need to find the p-value. We will use the =T.DIST.RT function to find the

p-value, because this is an upper tailed test.

In Excel input =T.DIST.RT(1.58785, 21) and hit Enter.

We used 21 as the degrees of freedom because when you have a 2-sample T Test

DF = n1 + n2 – 2 = 11 + 12 – 2 = 21.

We see this p-value = .06363. This is the same p-value we got using Excel and it is

still greater than .05, which means we get the same conclusion as above.

If we were running a Lower Tailed Test we would use =T.DIST(1.58785,21) to get

the p-value. And if we were running an Upper Tailed Test we would use

=T.DIST.2T(1.58785,21) to get the p-value. Remember if we are running a two

tailed test and the Test Stat is NEGATIVE, that is fine it can be. But if it is, then

you need to use the absolute value of the Test Stat in the function.

=T.DIST.2T(ABS(1.58785),21). Once you hit Enter you should see that this p-value

is also the same as the Excel output above.

This is how you would run a 2 – sample T hypothesis test using averages and

unequal unknown SD’s when we don’t have the raw data and can’t use Excel.

Now that we ran a hypothesis test, let calculate a confidence interval and draw

the same conclusion.

The equation for a 2 – sample T confidence interval:

�̅�1 − �̅�2 ± 𝑇𝛼 2

∗ ∗ √

𝑆𝐷1 2

𝑛1 +

𝑆𝐷2 2

𝑛2

Where Standard Error (SE) = √ 𝑆𝐷1

2

𝑛1 +

𝑆𝐷2 2

𝑛2

Margin of Error = 𝑇𝛼 2

∗ ∗ √ 𝑆𝐷1

2

𝑛1 +

𝑆𝐷2 2

𝑛2

We have the values we need let’s plug them into our equation.

76.2727 − 72.3333 ± 𝑇𝛼 2

∗ ∗ √

5.551413 2

11 +

6.343692 2

12

The last thing we need to find is a T-Critical Value. We will use the =T.INV in Excel

to find the T-Critical Value.

If alpha = .05 then 1 - .05 = .95. We will use this value in your Excel function.

=T.INV(.95,21)

We see the T – Critical Value is 1.720742. We will plug this into the equation and

solve. But if you compare this Critical Value to the Excel output we got when we

ran the hypothesis test it is the same because we used Alpha = .05 in the output.

But this value will change depending on what you input for Alpha.

t-Test: Two-Sample Assuming Unequal Variances

New Method Standard Method

Mean 76.27272727 72.33333333

Variance 30.81818182 40.24242424 Observations 11 12

Hypothesized Mean Difference 0

df 21

t Stat 1.587847328

P(T<=t) one-tail 0.063632175

t Critical one-tail 1.720742903

P(T<=t) two-tail 0.12726435

t Critical two-tail 2.079613845

Note: If we were running a two tailed test we would use =T.INV.2T(.05, 21) to get

the critical value for a two tailed test. Try this out and you should get the same

value as the Excel output.

76.2727 − 72.3333 ± 𝑇𝛼 2

∗ ∗ √

5.551413 2

11 +

6.343692 2

12

76.2727 − 72.3333 ± 1.72074 ∗ √ 5.551413

2

11 +

6.343692 2

12

3.9394 ± 1.72074 ∗ 2.48096

3.96667 ± 4.269087

The confidence interval goes from -1.1024 to 7.435757. This interval goes from a

negative value to a positive value. This means that 0 is in this interval. Because 0

is in the interval, No, it is Not Significant, and we Do Not Reject Ho. This is the

same conclusion that we got with the hypothesis test.