Statistical Analysis with Excel For Dummies. Joseph Schmuller
Чтение книги онлайн.

Читать онлайн книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller страница 21

Название: Statistical Analysis with Excel For Dummies

Автор: Joseph Schmuller

Издательство: John Wiley & Sons Limited

Жанр: Программы

Серия:

isbn: 9781119844563

isbn:

СКАЧАТЬ 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.

Snapshot of using SUM.

      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.

Snapshot of Excel opening a helpful menu.

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

      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.

Snapshot of using SUM with five arguments.

      FIGURE 2-5: Using SUM with five arguments.

      

If you select a cell in the same column as your data and just below the last data cell, Excel correctly guesses the data array you want to work on. Excel doesn't always guess what you want to do with that array, however. Sometimes when Excel does guess, its guess is incorrect. When either of those things happens, it’s up to you to enter the appropriate values into the Function Arguments dialog box.

      Quickly accessing statistical functions

      In this section, I show you how to create a shortcut to Excel’s statistical functions.

Snapshot of accessing Excel’s statistical functions.

      FIGURE 2-6: Accessing Excel’s statistical functions.

      Although Excel has buried the statistical functions several layers deep, you can use a handy technique to make them as accessible as any of the other categories — just add them to the Quick Access toolbar in the upper left corner.

Snapshot of adding the statistical functions to the Quick Access toolbar.

      FIGURE 2-7: Adding the statistical functions to the Quick Access toolbar.

Snapshot of accessing the Statistical Functions menu from the Quick Access toolbar.

      FIGURE 2-8: Accessing the Statistical Functions menu from the Quick Access toolbar.

From now on, whenever I deal with a statistical function, I assume you’ve created this shortcut so that you can quickly open the menu of statistical functions. The next section provides an example.

      On the iPad, no shortcut is necessary. To access statistical functions, it’s just Formulas | Statistical.

      Remember that on the iPad the Statistical icon is to the immediate right of Math & Trig.

      Array functions

      Most of Excel’s built-in functions are formulas that calculate a single value (like a sum) and put that value into a worksheet cell. Excel does have another type of function, however: It’s called an array function because it calculates multiple values and puts those values into an array of cells rather than into a single cell. I refer to this array as the output array.

      This is where Microsoft 365 and Office 2019 part company. In Office 2019 and in earlier versions of Excel, here’s how you work with an array function: СКАЧАТЬ