Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

To use Excel to create the expected matrix for a contingency table. To conduct a chi-square test for independence using p-values. EXCEL PROCEDURES: Experiment 1:

  • To use Excel to create the expected matrix for a contingency table.
  • To conduct a chi-square test for independence using p-values.

EXCEL PROCEDURES:

Experiment 1:

Use the data provided in the table below to test if the size of dog owned is independent of the gender of the owner.Use a 5% level of significance.

  1. Ho:

Ha:

Create the Actual Matrix:

  1. Open a new MS Excel workbook.
  2. In cell A2 type Small Dog, in cell A3 type Large Dog, in Cell B1 type Male, and in cell C1 type Female.
  3. Enter the data in the table below into the appropriate cells in your table in Excel.
Gender of owner
What type of pet do you prefer? Male Female
Small Dog 11 17
Large Dog 19 23
  1. In cell B4 type =sum(B2:B3).
  2. Select cell B4, hover over the black square in the lower right hand corner, left click and drag to the right to C4 to auto fill that cell.
  3. In cell D2 type =sum(B2:C2).
  4. Select cell D2, hover over the black square in the lower right hand corner, left click and drag down to cell D4 to auto fill those cells.

Create the Expected Matrix:

  1. In cell B6 type =B4*D2/D4
  2. In cell B7 type =B4*D3/D4
  3. In cell C6 type =C4*D2/D4
  4. In cell C7 type =C4*D3/D4

Find the degree of freedom, p-value, and chi-square test statistic for your hypothesis test:

  1. In cell A8 type "df"
  2. In cell B8 type =(COUNT(B2:B3)-1)*(COUNT(B3:C3)-1)
  3. In cell A9 type "p-value"
  4. Select cell B9.
    1. Select the Formulas tab ->More Functions->Statistical->CHISQ.TEST (or CHITEST in Excel 2007)
    2. Click in the field Actual_range, then select cells B2 through C3 by left clicking in cell B2 and dragging to C3.
    3. Click in the field Expected_range, then select cells B6 through C7 by left clicking in cell B6 and dragging to C7.
    4. Click OK.
  5. In cell A10 type "Chi-Sqr"
  6. In cell B10 type =CHISQ.INV.RT(B9,B8)
  7. Copy/paste the Excel table and statistics here.

  1. Conclusion (explain why and interpret in context).

Experiment 2:

Use the data provided in the table below to test if a person's favorite superhero is dependent on their occupation. Use a 5% level of significance.

  1. Ho:

Ha:

Create the Actual Matrix:

  1. In cell A12 type Wonder Woman, in cell A13 type Batman, in cell A14 type Superman, in Cell B11 type Photographer, in cell C11 type Reporter, and in cell D11 type Banker.
  2. Enter the data in the table below into the appropriate cells in your table in Excel.
Favorite superhero Photographer Reporter Banker
Wonder Woman 36 30 13
Batman 21 26 40
Superman 29 35 23

  1. In cell B15 type =sum(B12:B14).
  2. Select cell B15, hover over the black square in the lower right hand corner, left click and drag to the right to D15 to auto fill these cells.
  3. In cell E12 type =sum(B12:D12).
  4. Select cell E12, hover over the black square in the lower right hand corner, left click and drag down to cell E15 to auto fill those cells.

Create the Expected Matrix:

  1. In cell B17 type =B15*E12/E15
  2. In cell B18 type =B15*E13/E15
  3. In cell B19 type =B15*E14/E15
  4. In cell C17 type =C15*E12/E15
  5. In cell C18 type =C15*E13/E15
  6. In cell C19 type =C15*E14/E15
  7. In cell D17 type =D15*E12/E15
  8. In cell D18 type=D15*E13/E15
  9. In cell D19 type=D15*E14/E15

Find the degree of freedom, p-value, and chi-square test statistic for your hypothesis test:

  1. In cell A20 type "df"
  2. In cell B20 type =(COUNT(B12:B14)-1)*(COUNT(B12:D12)-1)
  3. In cell A21 type "p-value"
  4. Select cell B21.
    1. Select the Formulas tab ->More Functions->Statistical->CHISQ.TEST.
    2. Click in the field Actual_range, then select cells B12 through D14 by left clicking in cell B12 and dragging to D14.
    3. Click in the field Expected_range, then select cells B17 through D19 by left clicking in cell B17 and dragging to D19.
    4. Click OK.
  5. In cell A22 type "Chi-Sqr"
  6. In cell B22 type =CHISQ.INV.RT(B21,B20)
  7. Copy/paste the Excel table and statistics here.

  1. Conclusion (explain why and interpret in context).

SUMMARY:

  1. In general, are chi-square distributions normal, right skewed, or left skewed?

  1. What is the formula for calculating each expected frequency in each cell of the expected matrix?

  1. In tests of dependence, if the difference in the actual and expected matrices is too large to be due to chance alone (ie. conclusion is Reject Ho), will the p-value be smaller or larger than the significance level? _____________

  1. As the differences between the observed frequency and the expected frequency increase, does the value of the chi-square test statistic increase, or decrease? _________________

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Introduction to Probability

Authors: Mark Daniel Ward, Ellen Gundlach

1st edition

716771098, 978-1319060893, 1319060897, 978-0716771098

More Books

Students also viewed these Mathematics questions

Question

Rural Neighborhood Urban Yes 17 33 Obese No 49 31

Answered: 1 week ago

Question

4. In Exercise 3, are the random variables X and Y independent?

Answered: 1 week ago