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.
On the iPad it all looks quite a bit different, as Figure 1-5 shows.
FIGURE 1-5: Inserting a chart on the iPad.
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.
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.