Question
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.
- Ho:
Ha:
Create the Actual Matrix:
- Open a new MS Excel workbook.
- In cell A2 type Small Dog, in cell A3 type Large Dog, in Cell B1 type Male, and in cell C1 type Female.
- 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 |
- In cell B4 type =sum(B2:B3).
- 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.
- In cell D2 type =sum(B2:C2).
- 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:
- In cell B6 type =B4*D2/D4
- In cell B7 type =B4*D3/D4
- In cell C6 type =C4*D2/D4
- In cell C7 type =C4*D3/D4
Find the degree of freedom, p-value, and chi-square test statistic for your hypothesis test:
- In cell A8 type "df"
- In cell B8 type =(COUNT(B2:B3)-1)*(COUNT(B3:C3)-1)
- In cell A9 type "p-value"
- Select cell B9.
- Select the Formulas tab ->More Functions->Statistical->CHISQ.TEST (or CHITEST in Excel 2007)
- Click in the field Actual_range, then select cells B2 through C3 by left clicking in cell B2 and dragging to C3.
- Click in the field Expected_range, then select cells B6 through C7 by left clicking in cell B6 and dragging to C7.
- Click OK.
- In cell A10 type "Chi-Sqr"
- In cell B10 type =CHISQ.INV.RT(B9,B8)
- Copy/paste the Excel table and statistics here.
- 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.
- Ho:
Ha:
Create the Actual Matrix:
- 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.
- 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 |
- In cell B15 type =sum(B12:B14).
- 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.
- In cell E12 type =sum(B12:D12).
- 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:
- In cell B17 type =B15*E12/E15
- In cell B18 type =B15*E13/E15
- In cell B19 type =B15*E14/E15
- In cell C17 type =C15*E12/E15
- In cell C18 type =C15*E13/E15
- In cell C19 type =C15*E14/E15
- In cell D17 type =D15*E12/E15
- In cell D18 type=D15*E13/E15
- In cell D19 type=D15*E14/E15
Find the degree of freedom, p-value, and chi-square test statistic for your hypothesis test:
- In cell A20 type "df"
- In cell B20 type =(COUNT(B12:B14)-1)*(COUNT(B12:D12)-1)
- In cell A21 type "p-value"
- Select cell B21.
- Select the Formulas tab ->More Functions->Statistical->CHISQ.TEST.
- Click in the field Actual_range, then select cells B12 through D14 by left clicking in cell B12 and dragging to D14.
- Click in the field Expected_range, then select cells B17 through D19 by left clicking in cell B17 and dragging to D19.
- Click OK.
- In cell A22 type "Chi-Sqr"
- In cell B22 type =CHISQ.INV.RT(B21,B20)
- Copy/paste the Excel table and statistics here.
- Conclusion (explain why and interpret in context).
SUMMARY:
- In general, are chi-square distributions normal, right skewed, or left skewed?
- What is the formula for calculating each expected frequency in each cell of the expected matrix?
- 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? _____________
- 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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started