Posted by: tonyteaching | April 24, 2009

DIFFERENCES analysis between 2 populations (t-tes) – using Ms. Excel

For example, the Student Learning Center conducted survey among students and supervisors asking how many times they expected for the consultation time. And they got data like below.

The NULL hypothesis : “the means are equal

the ALTERNATIVE hypothesis : “the means are not equal”

We can analyze the Difference using t-test for small number of data or Z-test for big number of data

In Ms. Excel 2003 it is on “Tools” Menu > “Data Analysis”

In Ms. Excel 2007 it is on ‘Data’ tab > “Analysis” group > “Data Analysis” button

(if there is no that group, activate first from Office button > ‘Excel Options’ > ‘Add-In’ > ‘Excel Add-In’ button Go > Check ‘Analysis Toolpak’)

———————————————————————

Case 1. We just have small number of data (less then 30)

***

Students’ expectations (hours/month): 4,5,1,2,3,0,3,4,5,6,7,8,2,4,4,1,1,2,3

Supervisors’ expectations (hours/month): 1,2,3,1,4,4,2

***

1. First, put the data on the Excel cells

Students Expects. Supervisors Expects.
4 1
5 2
1 3
2 1
3 4
0 4
3 2
4
5
6
7
8
2
4
4
1
1
2
3

2. On ‘Tools‘ Menu > “Data Analysis

3. Choose: “t-Tes: Two-Sample Assuming Equal Variance

4. Fill in

variable 1 Range: cell range for data in column 1

variable 2 Range: cell range for data in column 2

Hypothesized Mean Difference: 0 (zero) …..it’s means the our ‘Null hyphotesis’ is

Alpha: 0.05 (later if we want to use p-value of difference parameter)

Output range: choose cell position where we want to put the analysis

then click ‘OK’

5. Then We will have:

t-Test: Two-Sample Assuming Equal Variances
Variable 1 Variable 2
Mean 3.421053 2.428571
Variance 4.590643 1.619048
Observations 19 7
Pooled Variance 3.847744
Hypothesized Mean Difference 0
df 24
t Stat 1.144349
P(T<=t) one-tail 0.131882
t Critical one-tail 1.710882
P(T<=t) two-tail 0.263764
t Critical two-tail 2.063899

Two options to analize the difference:

Option 1: based on t-value

From this table, let’s focus on

t stat is 1.144349

let’s compare with t Critical two-tail that is 2.063899 , it means

we accept the null hypothesis if t stat between -2.063899 and 2.063899

or the rejection rule: we reject the null hypothesis if t stat (less than) <-2.063899 or (more than) >2.063899

and because the ‘t stat’ is between the ‘t critical two-tail’ so we fail to reject the null hyphotesis or other word ‘we accept the null hyphotesis’ (the two populations’ means are equal)

——

Option 2: based on alpha-value

We can also draw this conclusion by comparing the p-value for a two-tail test and the alpha value.

We reject the null hypothesis if p-value is less than alpha value (here is 0.05) or

We accept the null hypothesis if p-value is greater than alpha value (here is 0.05).

Since the p-value 0.263764 is greater than a=0.05 again, we fail to reject the null hypothesis.

—-

Overall we can say, based on sample results, the two populations’ means are equal.

——————————————————————————————————

——————————————————————————————————

Case 2. We just have big number of data (more then 30) for n1 and n2 –> We use “Z-test

***

Students’ expectations (hours/month): 4,5,1,2,3,0,3,4,5,6,7,8,2,4,4,1,1,2,3,4,4,4,4,4,4,4,4,4,4,3,3,3,3,3,3,3,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1,1

Supervisors’ expectations (hours/month): 1,2,3,1,4,4,2,5,5,5,5,5,4,4,4,4,4,4,4,4,4,4,4,3,3,3,3,3,3,3,3,3,3,3,2,2,2,2,2,2,1,1,1,1,1,1

***

1. First, put the data on the Excel cells

2. Count the variance first

Use the Descriptive Statistics procedure to calculate the variances of the two samples. (on “Tools > “Data Analysis” > “Descriptive Statistics“)

The Excel procedure for testing the difference between the two population means will require information on the variances of the two populations.

Since the variances of the two populations are unknowns they should be replaced with sample variances.

The descriptive for both samples show that the variance of first sample is s12 = 2.72061, while the variance of the second sample s22 =1.621739

Students Expects. Supervisors Expects.
Mean 2.830189 Mean 2.978261
Standard Error 0.226566 Standard Error 0.187764
Median 3 Median 3
Mode 4 Mode 4
Standard Deviation 1.649427 Standard Deviation 1.273475
Sample Variance 2.72061 Sample Variance 1.621739
Kurtosis 0.909824 Kurtosis -1.01381
Skewness 0.789742 Skewness -0.16019
Range 8 Range 4
Minimum 0 Minimum 1
Maximum 8 Maximum 5
Sum 150 Sum 137
Count 53 Count 46
Confidence Level(95.0%) 0.454638 Confidence Level(95.0%) 0.378175

2. ‘Tools’ menu > ‘Data Analysis

3. Choose: “z-Test: Two Sample for Means

4. Fill in

variable 1 range: range cells for data in column 1

variable 2 range: range cells for data in column 2

Hypothesis Mean Difference : 0

Enter the variance of the first sample in the Variable 1 Variance box
Enter the variance of the second sample in the Variable 2 Variance box

select Labels
Enter 0.05 or, whatever level of significance you desire, in the Alpha box
Select a suitable Output Range for the results, I chose D2, then click OK.

We got

z-Test: Two Sample for Means
4 1
Mean 2.807692 3.022222222
Known Variance 2.72061 1.621739
Observations 52 45
Hypothesized Mean Difference 0
z -0.72171
P(Z<=z) one-tail 0.235235
z Critical one-tail 1.644854
P(Z<=z) two-tail 0.470471
z Critical two-tail 1.959964

The value of test statistic z=-0,72171

The rejection rule for this test is z < -1.959964 or z > 1.959964 from the normal distribution table.

Since the value of the test statistic z=-0,72171 is between the value of Z critical two-tail so we fail to reject the null hypothesis or in other word

We accept the Null hyphotesis= “The Means value is equal

——

We can also draw this conclusion by comparing the p-value for a two tail -test and the alpha value.

Since p-value 0.470471 is greater than a=0.05 we fail to reject the null hypothesis.

Overall we can say, based on the sample results, the two populations’ means are equal.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: