Statistical Analysis with Excel For Dummies. Joseph Schmuller

Performs a two-sample z-test to compare two means when the variances are known.

      The ToolPak is an add-in. To use it, you first have to load it into Excel. To start off that process (in the Windows version), choose File | Options from the main menu. Doing this opens the Excel Options dialog box. From there, follow these steps:

      1 In the Excel Options dialog box, select Add-Ins from the navigation menu on the left.Oddly enough, this step opens a list of add-ins.Near the bottom of the list, you see a drop-down list labeled Manage.

      2 From this list, select Excel Add-Ins, if it’s not already selected.

      3 Click Go.This step opens the Add-Ins dialog box. (See Figure 2-24.) The items in your dialog box are probably different from the ones in mine. That’s okay.

      4 Select the check box next to the Analysis ToolPak option as well as the check box next to the Solver Add-In option and then click OK.

      When Excel finishes loading the ToolPak and the Solver add-ins, you find the Data Analysis button and the Solver button in the Analyze area of the Data tab. It’s all the way on the right.

      The installation procedure for the Analysis ToolPak on the Mac is much simpler than the one for Windows.

      FIGURE 2-24: The Add-Ins dialog box.

To begin, choose Tools | Excel Add-ins from the main menu. (Figure 2-25 shows this menu choice.)

      FIGURE 2-25: The Tools | Excel Add-ins menu choice on the Mac.

This opens the Add-Ins dialog box. (See Figure 2-26.) Select the check box next to Analysis ToolPak and the checkbox next to Solver Add-In then click OK. That’s it. Data Analysis… and Solver… then appear as choices on the Tools menu.

      FIGURE 2-26: The Add-Ins dialog box on the Mac.

Follow these general steps for using a ToolPak data analysis tool:

      1 Enter your data into an array.

      2 Choose Data | Data Analysis from the main menu to open the Data Analysis dialog box.

      3 In the Data Analysis dialog box, select the data analysis tool you want to work with.

      4 Click OK (or just double-click the selection) to open the dialog box for the selected tool.

      5 In the tool's dialog box, enter the appropriate information.I know this step is vague, but each tool is different.

      6 Click OK to close the dialog box and see the results.

      Here's an example to get you accustomed to using these tools. In this example, I put the Descriptive Statistics tool through its paces. This tool calculates a number of statistics that summarize a set of scores.

      1 Enter your data into an array.Figure 2-27 shows an array of numbers in cells B2 through B9, with a column header in B1. The array is on a worksheet tab called Descriptive Statistics.

      2 Choose Data | Data Analysis from the main menu to open the Data Analysis dialog box.

      3 Click Descriptive Statistics and then click OK (or just double-click Descriptive Statistics) to open the Descriptive Statistics dialog box.FIGURE 2-27: Working with the Descriptive Statistics tool.

      4 Identify the data array.In the Input Range box, enter the cells that hold the data. In this example, that's B1 through B9. The easiest way to do this is to move the cursor to the top cell (B1), press the Shift key, and click in the bottom cell (B9). That puts the absolute reference format $B$1:$B$9 into the input range field.

      5 Select the Columns radio button to indicate that the data are organized by columns.

      6 Select the Labels in First Row check box because the input range includes the column heading.

      7 Select the New Worksheet Ply radio button, if it isn’t already selected.This step tells Excel to create a new tabbed sheet within the current worksheet and to then send the results to the newly created sheet.

      8 Select the Summary Statistics check box and leave the others deselected.

      9 Click OK.The new tabbed sheet (ply) opens, displaying statistics that summarize the data. Figure 2-28 shows the new ply, after you widen column A.

      For now, I don't tell you the meaning of each individual statistic in the Summary Statistics display. I leave that for Chapter 7, where I delve more deeply into descriptive statistics.

      FIGURE 2-28: The output of the Descriptive Statistics tool.

      Additional data analysis tool packages

      Two other data analysis tool packages are available for Excel. The good news is that both work on Windows, Mac, and iPad.

      One is called the XLMiner Analysis ToolPak (from Frontline Systems) and it does everything the original Toolpak does, plus an analysis called Logistic Regression. Rather than take you through this package in every chapter, I leave it until Chapter 21 when I cover Logistic Regression — and the XLMiner Analysis ToolPak provides the best way to get the job done.

      I use the iPad as an opportunity to explore the other package, StatPlus (from AnalystSoft). This way, you get a deep dive into another toolset, and I don’t have to cover every combination of every platform (Windows, Mac, iPad) with every tool package (Analysis Toolpak, XLMiner ToolPak, StatPlus) in every chapter!

      It turns out that the free version of StatPlus — the one I work with in this book — has some of the tools the Toolpak provides and also has some goodies the Toolpak lacks. That makes it very much worth having. Luckily for you, this add-in is easy to set up and to work with. All you have to do to add it to Excel on your iPad is tap Insert | Add-Ins | See All. In the Office Add-Ins box that appears, scroll down to StatPlus and tap Add.

When you want to use a StatPlus tool on the iPad, follow these steps:

      1 Enter your data into an array.It’s a good idea to name the tab of the worksheet that contains the array.

      2 Tap Insert | Add-ins | StatPlus to open the Add-ins box.If you’ve used StatPlus recently, its icon appears immediately under Recently Used Add-ins. If it doesn’t, find it in My Add-Ins. Tapping StatPlus opens the StatPlus pane on the right side of the screen.

      3 In the StatPlus panel, tap New Analysis to open the Commands box.

      4 Hunt down the analysis tool you want.The Commands box is organized into categories, so you might have to tap a couple of categories before you find the tool you’re looking for.

      5 Tap the name of the tool.

      6 Enter the values into the boxes on the StatPlus panel.From the drop-down menu for the Columns For box, select the tab where your data lives. If your spreadsheet is open to