Читать книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller - Страница 29

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, Mac, and iPad), you select only one cell, supply the appropriate information for the function, and just press Enter or click OK. Excel knows where the rest of the output array is and puts the computed values into its cells. It’s as if the computed values spilled over from the selected cell into the rest of the output array — so Microsoft refers to this process as spilling.

Throughout this book, I describe the Microsoft 365 procedure whenever I discuss an array function.

If you’re using Office 2019 (or an earlier version) of Excel, select the entire output array, supply the information that the function wants, and press Ctrl+Shift+Enter (Ctrl+Shift+Return or Command+Shift+Return on the Mac). I had to give you one last warning, for old times’ sake!

A good example of an array function is FREQUENCY (and it’s an Excel statistical function, too). Its job is to summarize a group of scores by showing how the scores fall into a set of intervals that you specify. For example, given the scores

77, 45, 44, 61, 52, 53, 68, 55

and the intervals

50, 60, 70, 80

FREQUENCY shows how many are less than or equal to 50 (2, in this example), how many are greater than 50 and less than or equal to 60 (that's 3), and so on. The number of scores in each interval is called a frequency. A table of the intervals and the frequencies is called a frequency distribution.

Here’s an example of how to use FREQUENCY in Microsoft 365:

1 Enter the scores into an array of cells.Figure 2-9 shows a group of scores in cells B2 through B16.FIGURE 2-9: Working with FREQUENCY.

2 Enter the intervals into an array.I’ve put the intervals in C2 through C9.

3 Select a cell to start the output array.I’ve put Frequency as the label in D1, so I select D2 to start the output array.

4 From the Statistical Functions menu, select FREQUENCY to open the Function Arguments dialog box.I use the shortcut I installed on the Quick Access toolbar to open this menu and select FREQUENCY.

5 In the Function Arguments dialog box, enter the appropriate values for the arguments.I begin with the Data_array box. In this box, I enter the cells that hold the scores. In this example, that's B2:B16. (I'm assuming you know Excel well enough to know how to do this in several ways.)Next, I identify the intervals array. FREQUENCY refers to intervals as bins and holds the intervals in the Bins_array box. In this example, C2:C9 goes into the Bins_array box. After I identify both arrays, the Insert Function dialog box shows the frequencies inside a pair of curly brackets: {}.

6 Click OK to close the Function Arguments dialog box and put the values in the output array.After you close the Function Arguments dialog box, the frequencies spill into the appropriate cells, as Figure 2-10 shows.

FIGURE 2-10: The finished frequencies.

If you select the first cell in the populated output array, the formula appears on the Formula bar in the usual way. If you select any other cell, the formula appears slightly grayer on the Formula bar.

On the iPad, you follow the same steps, but you complete some of them in a different way:

1 Enter the scores into an array of cells.No change. The numbers are in cells B2 through B16.

2 Enter the intervals into an array.Again, nothing different. The intervals are in C2 through C9.

3 Select a cell to start the output array.I tap cell D2.

4 From the Statistical Functions menu, select FREQUENCY to put the FREQUENCY formula into the Formula bar.This step highlights iPad's easier access to statistical functions. You just choose Formulas | Statistical and then select FREQUENCY. This step also shows the absence of dialog boxes from Excel on the iPad.

5 Use the formula in the Formula bar to enter the values of the arguments.In the Formula bar, the formula has the first argument, data_array, highlighted. (See Figure 2-11.) I select B2 through B16 to enter those cells into the formula's first argument.Next, I tap bins_array in the formula and select C2 through C9. If you find it difficult to select C2 and drag downward, select C9 and drag upward.

6 Press Return to put the values into the output array.

FIGURE 2-11: Working with FREQUENCY on the iPad.