Recipe5.7.Conducting ANOVA


Recipe 5.7. Conducting ANOVA

Problem

You'd like to conduct an analysis of variance (ANOVA).

Solution

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.

Discussion

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


See Also

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.


Recipe 5.8. Generating Random Numbers

Problem

You'd like to compute random numbers between specific upper and lower bounds and you're not sure how to do so in Excel.

Solution

Use Excel's built-in spreadsheet function RAND or VBA's Rnd function.

Discussion

RAND generates an evenly distributed random number between 0 and 1. Its syntax is =RAND( ) with no arguments. When this function is used in a spreadsheet, its value changes every time the spreadsheet is recalculated. For a random number that won't be recalculated, enter =RAND( ) in the formula bar and then press F9. This will enter a random number between 0 and 1 in the selected cell, instead of entering the formula =RAND( ). Therefore, the random value will remain unchanged when the spreadsheet is recalculated.

To compute a random number between two specific values, use the formula =RAND( )*(MaxVal-MinVal)+MinVal, where MinVal and MaxVal are the two values between which you want the random number to be generated. The result will be a real number. To generate a random integer, use =ROUND(RAND( )*(MaxVal-MinVal)+MinVal, 0).

You can use the VBA function Rnd in a similar manner. For example, the code x = (MaxX-MinX+1)*Rnd+MinX generates a random number between MinX and MaxX. In VBA, you should seed the random number generator by making a call to the Randomize function prior to using the Rnd function.

See Also

See Recipe 13.4 for an example that uses random numbers.

Generating random numbers can also be useful when selecting random samples from some population. Recipe 13.7 shows how to select random samples from a population where the probability of selecting any given sample is not uniform.

In cases where you need to draw samples from a finite population of discrete values, you should consider the Sampling tool . This tool is discussed in Recipe 5.9.