EXCEL

         Two-Sample Hypothesis Z-Test (Variances Known)

 

 

 

 

 

 

 

 

 

 

Perform a Two-Sample Z-Test:

Assumptions:

1.  Independent Samples.

2.  No assumption about the shape of either population is required.

3.  Variances for both populations are known.

4.  Both samples contain at least 30 observations.

 

 

 

 

 

 

 

 

 

 

The following are the grades of 30 students in two sections of a statistics class.

 

 

 

 

 

 

 

 

 

 

 

A

B

 

 

 

 

 

 

 

1

Section 1   Grade

Section 2   Grade

 

 

 

 

 

 

 

2

90

99

 

 

 

 

 

 

 

3

89

98

 

 

 

 

 

 

 

4

66

100

 

 

 

 

 

 

 

5

77

100

 

 

 

 

 

 

 

6

99.9

99.9

 

 

 

 

 

 

 

7

66

100

 

 

 

 

 

 

 

8

57

98

 

 

 

 

 

 

 

9

44

44

 

 

 

 

 

 

 

10

66

99

 

 

 

 

 

 

 

11

78

33

 

 

 

 

 

 

 

12

88

88

 

 

 

 

 

 

 

13

99

99

 

 

 

 

 

 

 

14

97

97

 

 

 

 

 

 

 

15

100

100

 

 

 

 

 

 

 

16

99

95

 

 

 

 

 

 

 

17

99

59

 

 

 

 

 

 

 

18

97

47.9

 

 

 

 

 

 

 

19

88

100

 

 

 

 

 

 

 

20

92

100

 

 

 

 

 

 

 

21

93

28.2

 

 

 

 

 

 

 

22

54

98

 

 

 

 

 

 

 

23

68

86

 

 

 

 

 

 

 

24

84

56

 

 

 

 

 

 

 

25

91

28

 

 

 

 

 

 

 

26

76

97

 

 

 

 

 

 

 

27

73

55

 

 

 

 

 

 

 

28

87

76

 

 

 

 

 

 

 

29

92

89

 

 

 

 

 

 

 

30

94

51

 

 

 

 

 

 

 

31

38

43

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Enter the data; place the grades for section 1 in column A and the grades for section 2 in

 

 

column B.  Variances are known to be 25 for each section.

 

 

 

 

 

 

 

 

 

 

 

 

Compare the grades of the two sections of a statistics class:

Perform a hypothesis test at the 5% significance level to determine if there exists a   

significant difference in grades of the two sections.

 

 

 

 

 

 

 

 

 

 

Perform Hypothesis Test with EXCEL:

 

 

1.  Enter the data into columns A and B.

 

 

2.  Select Tools and Data Analysis.

 

 

3.  Select Z-Test: Two-Sample for Means and click OK.

 

 

4.  Input the following in the dialog box:

 

 

    a.  Input Variable 1 RangeA1:A31.

 

    b.  Input Variable 2 Range:  B1:B31.

 

    c.  Hypothesized Mean Difference:  0.

 

    d.  Input Variable 1 Variance (known): 25

 

 

    e.  Input Variable 2 Variance (known): 25

 

 

    f.  Click on Labels (since you included the labels in your input range).

 

    g.  Alpha: 0.05.

 

    h.  Check Output Range and specify the range, if you would like the output on the same   

 

        spreadsheet. Output Range is A35.  Choose a New Work Sheet Ply if you like a

 

        new sheet.

 

 

    i.  Click OK and you will see the output below.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

z-Test: Two Sample for Means

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Section 1   Grade

Section 2   Grade

 

 

 

 

 

 

 

Mean

81.39666667

78.8

 

 

 

 

 

 

 

Known Variance

25

25

 

 

 

 

 

 

 

Observations

30

30

 

 

 

 

 

 

 

Hypothesized Mean Difference

0

 

 

 

 

 

 

 

 

z

2.011369351

 

 

 

 

 

 

 

 

P(Z<=z) one-tail

0.022143159

 

 

 

 

 

 

 

 

z Critical one-tail

1.644853476

 

 

 

 

 

 

 

 

P(Z<=z) two-tail

0.044286319

 

 

 

 

 

 

 

 

z Critical two-tail

1.959962787

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Interpret Your Results:

 

1. State the hypothesis:

 

 H0μ1 = μ2

 

 HA:  μ1  μ2

 

2.  Level of Significance:  α = 0.05 and we have a two-tailed test.

 

3. Determine Test Statistic:  z = 2.011

 

4. Decision Rule:  Critical z-Value:  Reject Ho if z > 1.96 or z< - 1.96.

 

5. Make a Decision:  Reject Ho since 2.011 does fall into the rejection region.

 

6. Conclusion:  There exists enough evidence at the 5% significance level to claim that there is

 

a significant difference in the grades of the two sections of statistics classes.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

copyright 2004 Elisabeth Knowlton