You'd like to conduct an analysis of variance (ANOVA).
The Analysis ToolPak offers three flavors of ANOVA: single factor, two-factor with replication, and two-factor without replication. Select Tools images/U2192.jpg border=0> Data Analysis from the main menu bar to access these tools.
By way of example, consider the hypothetical drug effectiveness data shown in Figure 5-19.
The data values represent some measure of merit, or effectiveness, for drugs A, B, and C, along with a control group. The Anova: Single Factor tool allows you to perform an analysis of variance for two or more groups, so we'll use that tool to test the hypothesis that there's no difference between the means of these groups.
After selecting the Anova: Single Factor tool from the Data Analysis dialog box, you should see the Anova: Single Factor dialog box like the one shown in Figure 5-20.
In the Input Range field, type or select the cell range containing all the data under consideration. Your dataset must be in contiguous columns for this tool. If you include the column labels as I did in this example, then be sure to check the "Labels in first row" option. You can set the Alpha parameter to whatever you'd like. In this case I left it at 5%.
Finally, choose your output options and press OK. Here, I chose to generate the output in the same worksheet, starting in cell E178. The results of this analysis are shown in Figure 5-21.
These results show descriptive statistics for each group, along with standard ANOVA information. ANOVA results are displayed for between groups and within groups. SS represents the sum of squared deviations from the mean. df represents degrees of freedom. MS is the mean square value. F is the F-Ratio. And finally, F crit is the critical F-value based on the F-distribution. In this example, the P-value is less than the supplied Alpha value, and the obtained F-value is greater than the critical F-value. These results imply that we should reject the null hypothesis that there's no significant difference between the means of these groups and conclude that there's some statistically significant difference.
Figure 5-19. Drug effectiveness
Figure 5-20. Anova: Single Factor dialog box
Figure 5-21. ANOVA results
To learn more about the Analysis ToolPak ANOVA tools, read the Excel help topic "About statistical analysis tools." Refer to any standard text that covers statistical tests, or tests of significance, to learn more about ANOVA and other statistical tests.
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Time Series Analysis
Curve Fitting and Regression
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations