LAB 13 INSTRUCTIONS & QUESTIONS FOR EXCEL 2007

CHI-SQUARE AND ANOVA

NAME______LABTIME______BLDG______

Find the data file, Lab13Spring07Data.xls, on the website. The table on sheet 1 providesdata to examine the relation between age and full-time or part-time student status. In order to assess the relationship you will construct the Chi-Square test piece by piece and then use an Excel function to verify your work.

  1. State the appropriate null and alternative hypotheses:

H0: ______

Ha: ______

  1. Find the marginal distribution for Ageby adding up each row and entering the totals in cells E4:E7. List the 4 values here:

Age / Total
15-19
20-24
25-34
35 and over
  1. Find the marginal distribution for Student status by adding up each column and entering the totals in cells C8:D8. List the 2 values here:

Student Status / Full-time / Part-time
Total
  1. Enter the grand total of all students in cell E8. List it here: ______

(At this point it would be wise to check your values in #2-4 because the rest of the lab depends on the accuracy of these totals!)

  1. The formula to calculate each expected value is:

(row total) x (column total)

grand total

You may calculate these by hand (using 2 decimal place answers) and put them into cells H4:I7 manually

ORUse Excel to find the expected values by entering “=$E4*C$8/$E$8” in cell H4. Then drag the formula in cell H4 down 3 rows and across 1 column to get the rest of the expected values for the other 7 cells.

Enter the expected values in the table below:

Expected Values / Full-time / Part-time
15-19
20-24
25-34
35 and over

(Optional: To verify you have done the expected values correctly you may add the rows and columns in the expected table to get the totals for the marginal distributions again. These totals you get should be the same as what you got in #2 and #3.)

  1. To calculate the Chi-Square test statistic, each cell contributes a portion to the overall value. The contribution from each cell is:

(Observed – Expected)2

Expected

To calculate the first cell’s contribution, in cell C13 enter “=(C4-H4)^2/H4”. Then drag the formula in cell L4 down 3 rows and across 1 column to get all eight cell contributions, one for each cell.

  1. The Chi-Square test statistic is obtained by adding up all these cell contributions. In cell D18, add up the 8 cell contributions, this is the Chi-Square test statistic. Enter Chi-Square value here: ______
  2. How many degree of freedom do we have? ______Put this#in cell D19.
  3. To calculate the P-value, in cell D20 enter “=chidist(D18,D19)”. Put the P-value here: ______
  4. Last, once the observed and expected values are both obtained, the “CHITEST” function automatically performs the Chi-Square test using these observed and expected values. To verify all your work in this portion, in cell G19 enter “=CHITEST(C4:D7,H4:I7)”. In should return the same P-value you got in #9. If it does not, there is a mistake somewhere in your previous work.Print this sheet to turn in with your questions.
  5. Using α=0.05, is there a relationship between age and student status?

YES NO (Circle one)

Write an appropriate conclusion here:

Sheet 2 of the Excel file provides scores for 3 groups of students each given one of 3 different teaching methods (Basal, DRTA, and Strat). An Analysis of Variance or ANOVA is needed to determine if the 3 teaching methods are different.

  1. While in sheet 2, click on the Data menu and select Data Analysis.
  2. In the pop-up window, select Anova: Single Factor and click ok.
  3. In the input section, for input range, enter “A2:C24”.
  4. Grouped by: “Columns” should be selected.
  5. Check the “Labels in first row” box.
  6. Alpha should be 0.05.
  7. In the output section, “Output range” should be selected, and in the box enter “E2”.
  8. Click ok and the results for the ANOVA will be displayed. Print this sheet to turn in with your questions.
  9. State the appropriate null and alternative hypotheses:

H0: ______

Ha: ______

  1. In order to validate the ANOVA, we need the standard deviations by taking the square root of the variance for each group. You may do this in Excel or by hand. Find the standard deviations rounded to 2 decimals:

Groups / Standard Deviation
Basal
DRTA
STRAT
  1. What is the ratio of the largest and smallest standard deviation? ______
  2. Is using ANOVA appropriate to compare the 3 means?

YES NO (Circle one)

  1. What is the F statistic? ______What is the P-value? ______
  2. Using α=0.05, are the means significantly different?

YES NO (Circle one)

Write an appropriate conclusion here: