Using Excel for Chi-Squared and ANOVA Hypothesis Tests: A Step-by-Step Guide, Exercises of Statistics

Download Using Excel for Chi-Squared and ANOVA Hypothesis Tests: A Step-by-Step Guide and more Exercises Statistics in PDF only on Docsity! 1 Using Excel, Chapter 11: Additional Hypothesis Tests Here we see how to use Excel to perform Chi-Squared and ANOVA tests. Each topic has its own page. • Chapter 11.1 - Chi-Squared Test for Goodness of Fit 2 • Chapter 11.2 - Chi-Squared Test of Independence 3 • Chapter 11.3 - ANOVA: There is the PC version and a Mac version. – PC: with the Analysis ToolPak 4 – Mac: with the StatPlus:mac LE software 5 • Loading the Analysis ToolPak (PC users) 6 • Downloading and running the StatPlus:mac LE software (Mac users) 6 2 Chi-Squared Test for Goodness of Fit • There is no single function that completes a chi-squared test in Excel from raw data. To get the test statistic, you really need to build the table then you can get the P -value or critical value with built-in functions. If you don’t need the test statistic, you can get the P -value directly with a single built-in function but you still need to calculate the expected frequencies. As such, there are two options. 1. Calculate the test statistic by building the appropriate table and then get the P -value or the critical value with built-in functions. – test statistic, χ2 = k∑ i=1 (Oi − Ei) 2 Ei . – P -value = CHIDIST( test statistic, df) – Critical-Value = CHIINV(significance level, df) 2. Immediately get the P -value given the observed and expected frequencies. – P -Value = CHITEST(observed cells, expected cells) – P -Value = CHISQ.TEST(observed cells, expected cells) (Excel 2010 or later) • Preliminary Example from Chapter 11.1: Here we tested to see if the outcomes in 60 rolls of a six-sided die fit our expectations from a fair die. We’ll use a 0.05 significance level. In that example we calculated the test statistic and got χ2 = 6.4. That was smaller than the critical value of 11.07 so we failed to reject the null hypothesis. Here we’ll let Excel do a lot of the work and get the P -value. Conclusion: The P -value (0.2692) is greater than α. We fail to reject the null hypothesis and don’t have enough evidence to conclude that this die is not fair. 5 ANOVA with the StatPlus:mac LE software (Mac Users) 1. Start the StatPlus:mac LE software (directions are on page 6). 2. Select Statistics from the menu bar. 3. Select Analysis of Variance (ANOVA) 4. Select One-way ANOVA (Simple) 5. Enter the data by highlighting all the columns. 6. Check labels in the first row if your top row contains the labels for the columns (it probably does). 7. (optional) Click preferences and choose the Alpha Value (significance level). 8. Click OK and you get a table of results. Over-Simplified Example, Case 1, from 11.3: Sample 1 Sample 2 Sample 3 3 3 4 3 5 5 4 5 6 5 5 7 5 7 8 Here we test for a difference in means between three samples. In the book we calculated the test statis- tic (F = 2.72) and let software determine the P -value (0.106). Following the directions above yields the follow- ing output with Excel and the StatPlus software. Interpretation: The sample means are 4, 5, and 6. The test statistic (F) is 2.727. The P -value of the test statistic is 0.106 which is larger than α. We fail to reject the null hypothesis that the means are equal. Conclusion: There is not enough evidence to conclude that the population means are different. 6 PC Users - Analysis ToolPak: The Analysis ToolPak, available with all PC versions of Excel, can be used for many of the tasks found in a standard statistics course. Many of these tasks can be done without the ToolPak, but they are generally much easier if you have it. Here is how to install the the Analysis ToolPak for PC’s. 1. Open a blank Excel spreadsheet. 2. Click on the windows icon (pre 2010) or the file tab (2010+). 3. Choose Excel Options (pre 2010) or just options (2010+). 4. Choose add-ins. 5. In manage (bottom of window), choose Excel Add-ins and click Go. 6. Check the box that says Analysis ToolPak and click OK. 7. After you load the Analysis ToolPak, the Data Analysis command is available under the Data tab. It should be the far right option. Mac Users - StatPlus: As of this writing, if you are running Excel 2008 or higher on a Mac, the Analysis ToolPak is not available. There is an application called StatPlus:mac LE which is a free version of the full StatPlus application. It can handle most of the tasks performed by the Analysis ToolPak and the full version is probably superior - but that costs money. Once you download the software from http://www.analystsoft.com/en/products/statplusmacle/, 1. Run StatPlus from the Applications Folder. 2. If you don’t already have Excel open, it will open it for you. 3. You will run the commands from the StatPlus menu (top menu bar). 4. Choose the appropriate cells from the Excel worksheet containing the data. 5. The results are printed to a separate Excel worksheet. 6. You can then cut and paste these results in the Excel Worksheet that contains the data.