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.
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.
What’s in a name? An array of possibilities
As you get more into Excel’s statistical features, you work increasingly with formulas that have multiple arguments. Oftentimes, these arguments refer to arrays of cells, as in the examples I describe earlier in this chapter.
If you attach meaningful names to these arrays, it helps you keep straight what you’re doing. Also, if you return to a worksheet after not working on it for a while, meaningful array names can help you quickly get back into the swing of things. Another benefit: If you have to explain your worksheet and its formulas to others, meaningful array names are tremendously helpful.
Excel gives you an easy way to attach a name to a group of cells (but not on the iPad). In Figure 2-12, column C is named Revenue_Millions, indicating “revenue in millions of dollars.” As it stands, that just makes it a bit easier to read the column. If I explicitly tell Excel to treat Revenue_Millions as the name of the array of cells C2 through C13, however, I can use Revenue_Millions whenever I refer to that array of cells.
FIGURE 2-12: Defining names for arrays of cells.
Why did I use Revenue_Millions and not Revenue (Millions) or Revenue In Millions or Revenue: Millions? Because Excel doesn’t like blank spaces or symbols in its names — that’s why. In fact, here are four rules to follow when you supply a name for a range of cells. The name
Must begin with an alphabetic character — a letter rather than a number or a punctuation mark.
Must be unique within the workbook.
Cannot
