Part IV: Managing and Analyzing Data
■ (^) Moving average
■ Random number generation
■ (^) Rank and percentile
■ Regression
■ (^) Sampling
■ T-test (three types)
■ (^) Z-test
As you can see, the Analysis ToolPak add-in brings a great deal of additional functionality
to Excel. These procedures have limitations, however. In some cases, you may prefer to cre-
ate your own formulas to do some calculations.
Installing the Analysis ToolPak Add-In
The Analysis ToolPak is implemented as an add-in. Before you can use it, though, you need
to make sure that the add-in is installed. Select the Data tab. If you see an Analyze group,
showing a Data Analysis button, the Analysis ToolPak is installed. If you can’t access Data
➪ Analyze ➪ Data Analysis, install the add-in by following these steps:
- Choose File ➪ Options. The Excel Options dialog box appears.
- Select the Add-Ins tab.
- At the bottom of the dialog box, select Excel Add-Ins from the Manage drop-
down list and then click Go. The Add-Ins dialog box appears. - Place a check mark next to Analysis ToolPak. There is no need to check the
add-in named Analysis ToolPak – VBA. - Click OK to close the Add-Ins dialog box.
Using the Analysis Tools
Using the procedures in the Analysis ToolPak add-in is relatively straightforward as long
as you’re familiar with the particular analysis type. To use any of these tools, choose Data
➪ Analyze ➪ Data Analysis, and the Data Analysis dialog box appears, as shown in Figure
33.1. Scroll through the list until you find the analysis tool that you want to use and then
click OK. A dialog box specific to the procedure that you select appears.Usually, you need to specify one or more input ranges, plus an output range. (Specifying
the top-left cell of the output range is sufficient.) Alternatively, you can choose to place
the results on a new worksheet or in a new workbook. The procedures vary in the amount of
additional information required. In many dialog boxes, you may be able to indicate whether
your data range includes labels. If so, you can specify the entire range, including the
labels, and indicate to Excel that the first column (or row) contains labels. Excel then uses