Statistical Analysis with Excel For Dummies. Joseph Schmuller
Читать онлайн книгу.
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.
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.
FIGURE 2-5: Using SUM with five arguments.
Quickly accessing statistical functions
In this section, I show you how to create a shortcut to Excel’s statistical functions.
You can get to Excel’s statistical functions by choosing Formulas | More Functions | Statistical and then choosing from the resulting pop-up menu. (See Figure 2-6.)
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.
To do this, choose Formulas | More Functions from the main menu and then right-click the Statistical option. From the pop-up menu that appears, pick the first option, Add to Quick Access Toolbar. (See Figure 2-7.) Doing this adds a button to the Quick Access toolbar. Clicking the new button’s down arrow opens the pop-up menu of statistical functions. (See Figure 2-8.)
FIGURE 2-7: Adding the statistical functions to the Quick Access toolbar.
FIGURE 2-8: Accessing the Statistical Functions menu from the Quick Access toolbar.
Here’s how to put Statistical formulas into the Quick Access toolbar on the Mac. Choose Excel | Preferences | Ribbon & Toolbar and then click the Quick Access Toolbar tab. On the Choose Commands From menu, choose All Commands. Scroll down the box on the left, choose Statistical and click > to move Statistical to the box on the right. Click Save to put the Statistical icon into the Quick Access toolbar in the green area above the ribbon.
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: Select the range of cells, which will be the output array, open the array function’s dialog box, fill in the appropriate values, and then press the key combination Ctrl+Shift+Enter (or Ctrl+Shift+Return or Command+Shift+Return on the Mac) to close the dialog box and populate the output array. Readers of previous editions of this book might recall my frequent admonitions and warnings about that key combination whenever I talked about array functions. You can still select the output array and use the key combination in Microsoft 365, but it’s no longer necessary.
In Microsoft 365 (Windows,