Lab R- Chi-Square -hypothesis testing

This lab uses Excel to crunch the numbers for the five problems in the Book (10.16-20). Then there are two additional problems in which the student must determine the values to enter into the worksheet to test the null hypothesis.

Part I:

Enter in the formula to make the worksheet automatically find the significance level if the Ho is to be accepted. All these formula should be entered into the WORKSHEET tab and checked by verifying that the values in RED at the top of page 2 are calculated correctly.

Note: some of the cells are already filled in, the one highlighted in RED need to be entered as follows:

1)For the expected averages enter these calculations into these cells

Cell / Variable / Formulae
E4 / / =C4/B4
F4 / / =D4/B4

2)For each Expected Count enter these calculations into these cells

Cell / Variable / Formulae
E5 / / =E$4*B5
F5 / / =F$4*B5

Then copy Cells E5:F5 down thru all the rows you have actual data for(down to row 6 or greater)

3)For each Chi Squared cell enter these calculations into these cells

Cell / Variable / Formulae
G5 / / =(E5-C5)^2/E5
H5 / / =(F5-D5)^2/F5

Then copy Cells G5:H5 down thru all the rows you have actual data for(down to row 6 or greater)

4)Finally enter the calculations for the Sum of all the Chi Squared cells, and the Degrees of Freedom

Cell / Variable / Formulae
G3 /
add up all the vaules in columns G and H / =SUM(G5:H20)
H3 /
/ =(COUNT(B5:B20)-1)

Note: the last entry in the Foumlae are extended to accomidate up to 16 rows of data

5)Test data: Enter these values into the Cells starting with “26” at C5

(the hypothetical columns are Yes/No, and the Rows are A,B,C)

Yes / No
A / 26 / 5
B / 36 / 7
C / 45 / 20

Your results should look like the top of the next page:

Significance between
Sum Chi / D.F. / High / Low
Row Total / yes / no / 4.135767 / 2 / 0.15 / 0.1
Col Total / 139 / 107 / 32 / 77% / 23% / Chi square
A / 31 / 26 / 5 / 23.86331 / 7.136691 / 0.191316588 / 0.639715
B / 43 / 36 / 7 / 33.10072 / 9.899281 / 0.253946984 / 0.849135
C / 65 / 45 / 20 / 50.03597 / 14.96403 / 0.506855479 / 1.694798

Part II:

1)Go to the AnswerSheet tab and copy the observed values (from problem 10.10) into your worksheet starting at Cell C5.

2)All the yellow, red and green cells should calculate out,

3)Finally you can make a decision to Accept (“A” in column K), or Reject (“R” in column L) your null hypothesis.

Part III:

Try two additional problems below, and enter the results into a new tab“Extra Answers”

(1)In the dataset "Popular Kids," students in grades 4 and 6 were asked whether good grades, athletic ability, or popularity was most important to them. A two-way table separating the students by grade and by choice of most important factor is shown below:

Grade 4 / Grade 6
Grades / 49 / 69
Popular / 24 / 38
Sports / 19 / 28

(2)A sample of 200 components is selected from the output of a factory that uses three different machines to manufacture these components. Each component in the sample is inspected to determine whether or not it is defective. The machine that produced the component is also recorded. The results are as follows:

Defective / Non-Defective
Machine A / 8 / 54
Machine B / 6 / 62
Machine C / 12 / 58

The manager wishes to determine whether or not there is a relationship between theproportion of defectives and the machine used.

Copy your calculationsfor each of the problems into the into the “Answer Sheet” tab and send to your teacher with 1R-xxx or 4R-xxx in the subject line.