LAB 13 INSTRUCTIONS QUESTIONS FOR EXCEL 2003 CHI SQUARE AND ANOVA NAME LABTIME BLDG Find the data file Lab13Spring07Data xls on the website The table on sheet 1 provides data 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 2 Find the marginal distribution for Age by 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 3 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 4 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 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 OR Use 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 5 Enter the expected values in the table below Expected Values Full time Part time 15 19 20 24 25 34 35 and over 6 7 8 9 10 11 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 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 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 ChiSquare test statistic Enter Chi Square value here How many degree of freedom do we have Put this in cell D19 To calculate the P value in cell D20 enter chidist D18 D19 Put the Pvalue here 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 Using 0 05 is there a relationship between age and student status YES NO Circle one Write an appropriate conclusion here ANOVA 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 12 13 14 15 16 17 18 19 20 While in sheet 2 click on the Tools menu and select Data Analysis In the pop up window select Anova Single Factor and click ok In the input section for input range enter A2 C24 Grouped by Columns should be selected Check the Labels in first row box Alpha should be 0 05 In the output section Output range should be selected and in the box enter E2 Click ok and the results for the ANOVA will be displayed Print this sheet to turn in with your questions State the appropriate null and alternative hypotheses H0 Ha 21 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 22 23 What is the ratio of the largest and smallest standard deviation Is using ANOVA appropriate to compare the 3 means YES NO 24 25 Circle one What is the F statistic What is the P value Using 0 05 are the means significantly different YES NO Circle one Write an appropriate conclusion here