Download Using Excel for Chi-Squared and ANOVA Hypothesis Tests: A Step-by-Step Guide and more Exercises Statistics in PDF only on Docsity!
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
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=
(Oi − Ei)^2 Ei
- P -value = CHIDIST( test statistic, df)
- Critical-Value = CHIINV(significance level, df)
- 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.
ANOVA with the Analysis ToolPak (PC Users)
- Load the Analysis ToolPak if you haven’t used it before (directions are on page 6).
- Select the Data tab from above the top menu bar.
- Select Data Analysis (far right of menu bar).
- Select ANOVA: Single Factor
- Enter the data by highlighting all the columns. Include the top row containing the column labels.
- Check labels if your top row contains the labels for the columns (it should).
- (optional) Enter a significance level (Alpha) as a decimal (usually .01, .05, or .10).
- Choose a location for the output.
- 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.
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.
ANOVA with the StatPlus:mac LE software (Mac Users)
- Start the StatPlus:mac LE software (directions are on page 6).
- Select Statistics from the menu bar.
- Select Analysis of Variance (ANOVA)
- Select One-way ANOVA (Simple)
- Enter the data by highlighting all the columns.
- Check labels in the first row if your top row contains the labels for the columns (it probably does).
- (optional) Click preferences and choose the Alpha Value (significance level).
- 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.
