Statistical Analysis with Excel For Dummies. Joseph Schmuller

box to what you see in Figure 1-4, a gallery of all possible Excel charts.

      FIGURE 1-4: The All Charts tab in the Insert Chart dialog box.

Chart is Excel’s name for graph.

On the iPad it all looks quite a bit different, as Figure 1-5 shows.

      FIGURE 1-5: Inserting a chart on the iPad.

      

Excel on the iPad doesn’t use dialog boxes — instead, the iPad version relies on pop-up menus. So, if you’re an iPad user and I tell you about a procedure that involves dialog boxes, bear in mind that you have to make some adjustments.

      To find the bulk of Excel’s statistical functionality, choose (on both Windows and Mac)

      Formulas | More Functions | Statistical

This is an extension of the shorthand. It means, “Select the Formulas tab, click the More Functions button, and then select the Statistical Functions choice from the pop-up menu that opens.” Figure 1-6 shows what I mean.

      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.

      

You won’t find the older functions on a Mac or Windows Statistical Functions menu. They have their own menu. To find it, choose Formulas | More Functions | Compatibility. On the iPad, tap Formulas | Compatibility. (The Compatibility icon is four icons to the right of the Statistical icon.)

      

Although I’m assuming you’re not new to Excel, I think it’s wise to take a little time and space to discuss Excel principles that figure prominently in statistical work. Knowing these fundamentals helps you work efficiently with Excel formulas. (If you’re an old hand at Excel, you can safely skip the next few sections.)

      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.