you will need this

Unit III:

Chi-Square Testing (Excel 2010 & 2007)

1. Open a new Excel worksheet (which will be saved as CHISQUARE.xlsx).In cell A1 type name.In cell A2 type the course and section number (i.e. ECON225-01).In cell A3 type the date.Leave cell A4 blank.In cell A5 type "Assignment:Chi-Square Testing".In cell A6 type "File: CHISQUARE.xlsx".

2. Select cell C8 and type OBSERVED FREQUENCIES

Select cell B9 and type -- Age/Status.

Select cell B11 and type 16-20(Center format all for more professional appearance.)

Select cell B12 and type 21-34

Select cell B13 and type 35-49

Select cell B14 and type >50

Select cell C10 and type Claim

Select cell D10 and type No Claim

3. In cells C11 through C14 type the numbers 50, 45, 38, and 34 respectively.

In cells D11 through D14 type the numbers125, 176, 93 and 68 respectively.

(Center format all for more professional appearance.)

4. Compute the sum of the rows and the sum of the columns in the following way:

Select the entire column C11 through C14 and then click on the AutoSum function () in the Toolbar.The sum should appear in C15.

Repeat the step above to AutoSum the remaining two columns, three rows, and the grand total.(**The cells being summed will be shown.If cells other than what you want are being summed, make the correction in the formula shown at the top of the screen in the window next to fx.)

5. Select cell C17 and type EXPECTED FREQUENCIES

Select cell B18 and type ----- Age /Status

Select cell B20 and type 16-20(Center format all for more professional appearance.)

Select cell B21 and type 21-34

Select cell B22 and type 35-49

Select cell B23 and type >50

Select cell C19 and type Claim

Select cell D19 and type No Claim

6. To compute the expected frequencies for each cell in the Chi-square table using the formula (row total x column total) / grand total, enter the following:

Select C20, and type =$E11*C$15/629. Hit Enter.

Position the mouse handler over the lower right corner of selected cell C20

(Instructions continue on the next page)

until the handler changes its shape (to a dark + ).Hold down the left button on your mouse and drag it over the eight cells C20, D20, C21, D21, E21, C22, D22, C23 and D23.

7. If the Expected Frequency values did not print to one place after the decimal, select cells C20 through D23 and click on the Decrease Decimal key under "Number" in the toolbar until the table values have only one place after the decimal.Center format all for more professional appearance.

8. Compute the sum of the rows, the sum of the columns, and the grand total as explained in Step 4 above.(Carefully check that the cells being summed are the ones you want to sum.If they are not, change the cells to be summed shown in the formula box above the column headings.)Each row total, column total, and grand total value should equal the totals calculated in the Observed Frequencies table.Center format all for more professional appearance.

9. Select cell B26 and type Fo.Select cell C26 and type Fe.Select cell D26 and type (Fo-Fe)squared/ Fe .

10. Beginning in cell B27, type the Observed Frequencies in cells B27 through B34, respectively.Next, in cells C27 through C34 type the Expected Frequencies (enter #s to one place after the decimal)calculated in Step 8 above).Do not type the row totals, column totals, or grand total values and be careful to go in the same direction through both the observed and expected frequencies tables as you type the corresponding values for each cell.

11. Select D27, then type =(B27-C27)^2/C27.Hit Enter.Next, position the mouse handler over the lower right corner of selected cell D27 until the handler changes its shape to a dark + .Hold down the left button on your mouse and drag it down the column, highlighting cells D27 through D34.Hit Enter.

12. Select cell D35 and click on the AutoSum function () at the top of screen.The value that appears in cell D35 will be the computed chi-square statistic.Select cell E35 and type Computed Chi-Square Statistic.Center format cells B26 through D35.

13. Select cell B38. Then click on the Formulas tab in the toolbar, then select More Functions. Under the Function category select Statistical.Under the Function name select CHISQ.INV.RT (for Excel 2010) or select CHIINV (for Excel 2007).In the dialog boxes type .10 in the Probability dialog box and type 3 in the Degrees of freedom dialog box.Click on OK .The table chi-square statistic will appear in B38. Type Table Chi-square Statistic for Alpha = .10 in cell C38.

(Instruction continue on the next page)

14. Select cell B39. Then click on the Formulas tab in the toolbar, then select More Functions. Under the Function category select Statistical.Under the Function name select CHISQ.INV.RT (for Excel 2010) or select CHIINV (for Excel 2007).

In the dialog boxes type .02 in the Probability dialog box and type 3 in the Degrees of freedom dialog box.Click on OK. The table chi-square statistic will appear in B39. Type Table Chi-square Statistic for Alpha = .02 in cell C39.

15. Save your worksheet on a disk as CHISQUARE.xlsx and print-out the worksheet to submit to the instructor.

16. In addition to submitting a print-out of the worksheet, also submit the following, referencing the data in your print-out and your textbook or Notes:

Suppose that an insurance company is wondering if a person's age influences whether or not they file insurance claims.To test the hypothesis, the insurance company gathers the data shown in your Observed Frequencies table.

(1) State the null and alternative hypotheses (use your variable names).

(2) a.State the decision rule at the .10 significance level.

b.Draw the chi-square graph, insert the table and computed chi-

square statistics, insert the confidence level value and signifi-

cance level value, and label the Accept and Reject regions.

c.Should the insurance company accept or reject H0 at the .10

significance level?Type . answer next to or beneath the

requested graph for question (2. b.).

d.What type of error can possibly be made? State with part c.

(3) a.State the decision rule at the .02 significance level.

b.Draw the chi-square graph, insert the table and computed chi-

square statistics, insert the confidence level value and signifi-

cance level value, and label the Accept and Reject regions.

c.Should the insurance company accept or reject H0 at the .02

significance level?Type answer next to or beneath the

requested graph for question (3.b.).

d.What type of error can possibly be made?State with part c.