Joseph Schmuller - Statistical Analysis with Excel For Dummies

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.

Because of the way pre-Ribbon versions of Excel were organized, the Insert Function dialog box used to be extremely useful. Now it’s mostly helpful if you’re not sure which function to use or where to find it.

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 final selection on each category menu (like the Statistical Functions menu) is the Insert Function command. Choosing this option is still another way to open the Insert Function dialog box. (The Mac version refers to this dialog box as the Formula Builder.)

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.

In this section, I show you how to use the worksheet functions and the analysis tools.

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, SUMis 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 the arguments.Excel guesses that you want to sum the numbers in cells C2 through C8 and identifies that array in the Number1 box. Excel doesn't keep you in suspense: The Function Arguments dialog box shows the result of applying the function. In this example, the sum of the numbers in the array is 250. (See Figure 2-3.)

4 Click OK to put the sum into the selected cell.

Note a couple of points. First, as Figure 2-3 shows, the Formula bar holds

=SUM(C2:C8)

This formula indicates that the value in the selected cell equals the sum of the numbers in cells C2 through C8.

FIGURE 2-3:Using SUM.

After you become familiar with a worksheet function and its arguments, you can bypass the menu and type the function directly into the cell or into the Formula bar, beginning with an equal sign (=). When you do, Excel opens a helpful menu as you type the formula. (See Figure 2-4.) The menu shows possible formulas beginning with the letter(s) you type, and you can choose one by double-clicking it.

FIGURE 2-4:As you type a formula, Excel opens a helpful menu.

Another noteworthy point is the set of boxes in the Function Arguments dialog box. (Refer to Figure 2-3.) In the figure, you see just two boxes: Number1 and Number2. The data array appears in Number1 — what's Number2 for?

The Number2 box allows you to include an additional argument in the sum. And it doesn’t end there: Click in the Number2 box, and the Number3 box appears. Click in the Number3 box, and the Number4 box appears — and on and on. The limit is 255 boxes, with each box corresponding to an argument. A value can be another array of cells anywhere in the worksheet, a number, an arithmetic expression that evaluates to a number, a cell ID, or a name you have attached to a range of cells. (Regarding that last one: Read the later section “ What’s in a name? An array of possibilities.”) As you type values, the Function Arguments dialog box shows the updated sum. Clicking OK puts the updated sum into the selected cell.

You won't find this multi-argument capability on every worksheet function. Some are designed to work with just one argument. For the ones that work with multiple arguments, however, you can incorporate data that reside all over the worksheet. Figure 2-5 shows a worksheet with a Function Arguments dialog box that includes data from two arrays of cells, two arithmetic expressions, and one cell. Notice the format of the function in the Formula bar — a comma separates successive arguments.