Statistical Analysis with Excel For Dummies. Joseph Schmuller
Читать онлайн книгу.
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 contain spaces or symbols (as I just mentioned) — use an underscore to denote a space between words in the name.
Cannot duplicate any cell reference in the worksheet.
Otherwise, Excel gives you up to 255 characters to get creative with the name.
Here’s how to define a name:
1 Add a descriptive name at the top of a column (or to the left of a row) you want to name.(Refer to Figure 2-10.)
2 Select the range of cells you want to name.In this example, that’s cells C2 through C13. Why not include C1? I explain in a second.
3 Right-click the selected range.This step opens the pop-up menu shown in Figure 2-13.FIGURE 2-13: Right-clicking a selected cell range opens this pop-up menu.
4 From this pop-up menu, choose Define Name.This selection opens the New Name dialog box. (See Figure 2-14.) As you can see, Excel knows that Revenue_Millions is the name of the array and that Revenue_Millions refers to cells C2 through C13.