In this chapter, I introduce you to Excel's statistical functions and data analysis tools. If you’ve used Excel, and I'm assuming you have, you’re aware of Excel’s extensive functionality, of which statistical capabilities are a subset. You can either enter a piece of data into each worksheet cell, instruct Excel to carry out calculations on data that reside in a set of cells, or use one of Excel’s worksheet functions to work on data. Each worksheet function is a built-in formula that saves you the trouble of having to direct Excel to perform a sequence of calculations. As newbies and veterans know, formulas are the business end of Excel. The data analysis tools go beyond the formulas. Each tool provides a set of informative results.
Getting Started
Many of Excel’s statistical features are built into its worksheet functions. Clicking the Excel Insert Function button (it’s labeled fx) opens the Insert Function dialog box, which presents a list of Excel’s functions and the capability to search for Excel functions. (On the Mac, this button opens the Formula Builder, which is pretty much the same thing — except it’s a pane rather than a dialog box, meaning that you can keep it open while you work. On the iPad, fx opens a pop-up menu of Excel functions.) Although Excel now provides easier ways to access the worksheet functions, the latest version preserves this button and offers additional ways to open the Insert Function dialog box. I discuss all of this in more detail in a moment.
Figure 2-1 shows the two locations of the Insert Function button. (Note the Formula bar as well.) Along with one Insert Function button, the Formula bar is to the right of the Name box. All three are just below the Ribbon.
FIGURE 2-1: The Function library, the Name box, the Formula bar, the two Insert Function buttons, the Enter button, and the Cancel button.
Near the Name box, just to the left of the Insert Function button, you find an X and a check mark. The X is the Cancel button, and the check mark is the Enter button. Clicking the Enter button is like pressing the Enter key on the keyboard: It tells Excel to perform a computation you’ve typed into a cell. Clicking the Cancel button removes anything you’ve typed into a cell — just as long as you haven’t clicked that Enter button yet. On the iPad, the X and the check mark are to the right of the Formula bar.
Inside the Ribbon, on the Formulas tab, is the Function library. Mac users see a similar layout.
The Formula bar is sort of a clone of any cell you select — information entered into the Formula bar goes into the selected cell, and information entered into the selected cell appears on the Formula bar. You can edit the selected cell’s contents in either the cell or in the Formula bar. The Formula bar provides more room, but the cell can display text at a larger size if you zoom in on the worksheet.
Figure 2-1, shown earlier, shows Excel with the Formulas tab open. With that tab open, you can see the other location for the Insert Function button. Labeled fx, it’s on the extreme left end of the Ribbon, in the Function library area. As I mention earlier in this section, when you click the Insert Function button, you open the Insert Function dialog box. (See Figure 2-2.)
FIGURE 2-2: The Insert Function dialog box.
This dialog box enables you to find a function that fits your needs by either typing a search term or by scrolling a list of Excel functions.
In addition to clicking the Insert Function button next to the Formula bar, you can open the Insert Function dialog box by choosing Formulas | Insert Function from the main menu.
The Function library presents the categories of formulas you can use and makes it convenient for you to access them. Clicking a category button in this area opens a menu of functions in that category.
Most of the time, I work with statistical functions that are easily accessible from the Statistical Functions menu. Sometimes I work with math functions on the Math & Trig Functions menu. (You see a couple of these functions later in this chapter.) In Chapter 5, I show you how to use a couple of logic functions.
The Name box is sort of a running record of what you do in the worksheet. Select a cell, and the cell’s address appears in the Name box. Click the Insert Function button, and the name of the function you selected most recently appears in the Name box.
In addition to its statistical functions, Excel provides a number of data analysis tools that you access from the Data tab’s Analysis area.
Setting Up for Statistics
In this section, I show you how to use the worksheet functions and the analysis tools.
Worksheet functions
As I point out in the preceding section, the Function library area of the Formulas tab shows all categories of worksheet functions.
The steps in using a worksheet function are:
1 Type your data into a data array and select a cell for the result.
2 Select the appropriate formula category and choose a function from its pop-up menu.Completing this step opens the Function Arguments dialog box.
3 In the Function Arguments dialog box, type the appropriate values for the function’s arguments.Argument is a term from mathematics. It has nothing to do with debates, fights, or confrontations. In mathematics, an argument is a value on which a function does its work.
4 Click OK to put the result into the selected cell.
Yes, that’s all there is to it.
To give you an example, I explore a function that typifies how Excel’s worksheet functions work. This function, SUM, adds up the numbers in cells you specify and returns the sum in still another cell you specify. Although adding numbers together is an integral part of statistical number-crunching, SUM is not in the Statistical category. It is, however, a typical worksheet function, and it shows a familiar operation.
Here, step by step, is how to use SUM:
1 Enter your numbers into an array of cells and select a cell for the result.In this example, I've entered 45, 33, 18, 37, 32, 46, and 39 into cells C2 through C8, respectively, and selected C9 to hold the sum.
2 Select the appropriate formula category and choose the function from its pop-up menu.This step opens the Function Arguments dialog box.I chose Formulas | Math & Trig and scrolled down to find and choose SUM.
3 In the Function Arguments dialog box, enter the appropriate values for