Chapter 1 AL Evaluating Data in the Real World In This Chapter RI ▶ Introducing statistical concepts ▶ Generalizing from samples to populations TE ▶ Getting into probability ▶ Making decisions MA ▶ New features in Excel 2007 ▶ Understanding important Excel Fundamentals D ▶ New features in this edition TE T GH he field of statistics is all about decision-making — decision-making based on groups of numbers.
10 Part I: Statistics and Excel: A Marriage Made in Heaven By shouldering the number-crunching load, software increases our speed of traveling down that path. Some software packages are specialized for statistical analysis and contain many of the tools that statisticians use. Although not marketed specifically as a statistical package, Excel provides a number of these tools, which is why I wrote this book. I said that number crunching is a small part of the path to sound decisions.
Chapter 1: Evaluating Data in the Real World Estimating the population average is one kind of inference that statisticians make from sample data. I discuss inference in more detail in the upcoming section “Inferential Statistics.” Now for some terminology you have to know: Characteristics of a population (like the population average) are called parameters, and characteristics of a sample (like the sample average) are called statistics.
12 Part I: Statistics and Excel: A Marriage Made in Heaven group receives instruction via the new method, the other receives instruction via traditional methods. Before and after both groups receive instruction, the researcher measures the reading speeds of all the children in this study. What happens next? I get to that in the upcoming section entitled “Inferential Statistics: Testing Hypotheses.” For now, understand that the independent variable here is Method of Instruction.
Chapter 1: Evaluating Data in the Real World Interval data provides equal differences. Fahrenheit temperatures provide an example of interval data. The difference between 60 degrees and 70 degrees is the same as the difference between 80 degrees and 90 degrees. Here’s something that might surprise you about Fahrenheit temperatures: A temperature of 100 degrees is not twice as hot as a temperature of 50 degrees.
14 Part I: Statistics and Excel: A Marriage Made in Heaven the total number of events that can happen. In each of the three examples, the event we were interested in (head, 3, or club) only happens one way. Things can get a bit more complicated. When you toss a die, what’s the probability you roll a 3 or a 4? Now you’re talking about two ways the event you’re interested in can occur, so that’s (1 + 1)/6 = 2/6 = 1/3.
Chapter 1: Evaluating Data in the Real World That decision is wrapped in a conditional probability question — what’s the probability of obtaining the data, given that this hypothesis is correct? Statistical analysis provides tools to calculate the probability. If the probability turns out to be low, the statistician rejects the hypothesis. Here’s an example. Suppose you’re interested in whether or not a particular coin is fair — whether it has an equal chance of coming up heads or tails.
16 Part I: Statistics and Excel: A Marriage Made in Heaven With the hypotheses in place, toss the coin 100 times and note the number of heads and tails. If the results are something like 90 heads and 10 tails, it’s a good idea to reject H0. If the results are around 50 heads and 50 tails, don’t reject H0. Similar ideas apply to the reading-speed example I gave earlier.
Chapter 1: Evaluating Data in the Real World Although not likely, those errors are possible. They lurk in every study that involves inferential statistics. Statisticians have named them Type I and Type II. If you reject H0 and you shouldn’t, that’s a Type I error. In the coin example, that’s rejecting the hypothesis that the coin is fair, when in reality it is a fair coin. If you don’t reject H0 and you should have, that’s a Type II error.
18 Part I: Statistics and Excel: A Marriage Made in Heaven What’s New in Excel? The big news in Excel 2007 — throughout Microsoft Office 2007, in fact — is the user interface. Where a bar of menus once ruled, you now find a tabbed band. Appearing near the top of the worksheet window, this band is called the Ribbon. Figure 1-2 shows the appearance of the Ribbon after I select the Insert tab. Figure 1-2: The Insert Tab in the Ribbon in Excel 2007.
Chapter 1: Evaluating Data in the Real World Figure 1-3: Clicking Insert | Other Charts opens this gallery. The downside to all this, of course, is the Ribbon’s newness. If you’ve spent years with previous versions, you’ve developed an overall sense of where frequently used capabilities reside. Now you have to reorient: The switch from the menu bar to the Ribbon relocates almost everything. It’s worth your while to reorient.
20 Part I: Statistics and Excel: A Marriage Made in Heaven 2. Select a function from the Statistical Functions menu to open a dialog box for that function. 3. Enter the required information into the dialog box. 4. Close the dialog box. Again I’ve oversimplified, and again not by much, as you see throughout the book. Statistical Functions menu? Yep. This time around, you have a Statistical Functions menu that wasn’t in the earlier incarnations.
Chapter 1: Evaluating Data in the Real World Figure 1-4: Expenditures for R&D in science and engineering. 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 “=”.) Press Enter and the total appears in H2.
22 Part I: Statistics and Excel: A Marriage Made in Heaven Dragging isn’t the only way to do it. Another way is to select the array of cells you want to autofill (including the one that contains the formula), and click the down arrow next to Home | Fill This opens the Fill pop-up menu (see Figure 1-5). Select Down and you accomplish the same thing as dragging and dropping. Figure 1-5: The Fill pop-up menu. Still another way is to select Series from the Fill pop-up menu.
Chapter 1: Evaluating Data in the Real World = D2 + E2 + F2 + G2 After autofill, the formula in H3 is = D3 + E3 + F3 + G3 and the formula in H4 is . . . well, you get the picture. This is perfectly appropriate. I want the total in each row, so Excel adjusts the formula accordingly as it automatically inserts it into each cell. This is called relative referencing — the reference (the cell label) gets adjusted relative to where it is in the worksheet.
24 Part I: Statistics and Excel: A Marriage Made in Heaven The story is this: unless you tell it not to, Excel uses relative referencing when you autofill. So the formula inserted into I3 is not =H3/H11 Instead, it’s =H3/H12 Why does H11 become H12? Relative referencing assumes that the formula means divide the number in the cell by whatever number is nine cells south of here in the same column. Because H12 has nothing in it, the formula is telling Excel to divide by zero, which is a no-no.
Chapter 1: Evaluating Data in the Real World reference for both the row and column in the address ($H$11), absolute reference for the row-part only (H$11), and absolute reference for the column-part only ($H11). What’s New in This Edition? Although Excel’s statistical functions haven’t changed, I’ve restructured the instructions for every statistical function. The instructions in this edition fit in with the steps I outlined in the preceding section.
26 Part I: Statistics and Excel: A Marriage Made in Heaven
