Statistical Analysis with Excel For Dummies. Joseph Schmuller

Чтение книги онлайн.

      FIGURE 1-6: Accessing the Statistical Functions menu.

      In Chapter 2, I show you how to make the Statistical Functions menu more accessible.

      On the iPad, it’s a slightly different story. Surprisingly, Excel on the iPad makes statistical functions a bit more accessible than on Windows or the Mac. It’s just

      Formulas | Statistical

but you tap the Statistical icon (the word Statistical isn’t onscreen), as Figure 1-7 shows.

      FIGURE 1-7: Accessing the Statistical functions on the iPad.

      Back in 2010, Microsoft changed the way Excel names its functions. The objective was to make a function’s purpose as obvious as possible from its name. Excel also changed some of the programming behind these functions to make them more accurate.

      Excel continues this naming style while maintaining the older statistical functions (pre-2010 vintage, and one — FORECAST — from 2013) for compatibility with older versions of Excel.

      Autofilling cells

      The first fundamental is autofill — Excel's capability for repeating a calculation throughout a worksheet. Insert a formula into a cell, and you can drag that formula into adjoining cells.

Figure 1-8 is a worksheet of expenditures for R&D in science and engineering at colleges and universities for the years shown. The data, taken from a U.S. National Science Foundation report, are in millions of dollars. Column H holds the total for each field, and Row 11 holds the total for each year. (I tell you more about column I in a moment.)

      I started with column H blank and with row 11 blank. How did I get the totals into column H and row 11?

      If I want to create a formula to calculate the first row total (for Physical Sciences), one way (among several) is to enter

       = D2 + E2 + F2 + G2

      into cell H2. (A formula always begins with an equal sign: =.) Press Enter and the total appears in H2.

      FIGURE 1-8: Expenditures for R&D in science and engineering.

Now, to put that formula into cells H3 through H10, the trick is to position the cursor in the lower right corner of H2 until a plus sign (+) appears, hold down the left mouse button, and drag the mouse through the cells. That plus sign is called the cell's fill handle.

      When you finish dragging, release the mouse button and the row totals appear. This saves huge amounts of time because you don’t have to reenter the formula eight times.

      Same thing with the column totals. One way to create the formula that sums up the numbers in the first column (1990) is to enter

       =D2 + D3 + D4 + D5 + D6 + D7 + D8 + D9 + D10

      into cell D11. Position the cursor on D11’s fill handle, drag through row 11 and release in column H, and you autofill the totals into E11 through H11.

      Dragging isn't the only way to do it. Another way is to select the range of cells you want to autofill (including the one that contains the formula) and click

       Home | Fill

      (Fill is in the Home tab’s Editing area.)

Clicking Fill opens the Fill pop-up menu. (See Figure 1-9.) Select Down and you accomplish the same result as dragging and dropping.

      FIGURE 1-9: The Fill pop-up menu.

Still another way is to choose Series from the Fill pop-up menu. Doing this opens the Series dialog box. (See Figure 1-10.) In this dialog box, select the AutoFill radio button and click OK, and you’re all set. This method takes one more step, but the Series dialog box is a bit more compatible with earlier versions of Excel.

      FIGURE 1-10: The Series dialog box.

      I bring this up because statistical analysis often involves repeating a formula from cell to cell. The formulas are usually more complex than the ones in this section, and you might have to repeat them many times, so it pays to know how to autofill.

      

A quick way to autofill is to click in the first cell in the series, move the cursor to that cell’s lower right corner until the autofill handle appears, and double-click. This works on both PCs and Macs.

On the iPad, you select a cell and dots appear in the cell’s upper left and lower right corners. These dots are called selection handles. Tap the cell to open a pop-up menu. On the pop-up menu, tap Fill, touch the lower right selection handle, and drag in the desired direction (in this case, downward) to populate the cells. (See Figure 1-11.)

      

Notice that the iPad pop-up menu is laid out horizontally rather than vertically, as in traditional Microsoft Office applications. If you have an iPad Pro and a Magic keyboard as well, a two-finger click on the track pad opens a traditional-style (vertically arrayed) pop-up menu. If you go full-on old school and connect a mouse to your iPad СКАЧАТЬ