Part 1: Getting Started with Statistical Analysis with Excel: A Marriage Made In Heaven

In Part 1, I provide a general introduction to statistics and to Excel’s statistical capabilities. I discuss important statistical concepts and describe useful Excel techniques. If it’s a long time since your last course in statistics or if you’ve never had a statistics course at all, start here. If you haven’t worked with Excel’s built-in functions (of any kind), definitely start here.

Part 2: Describing Data

Part of statistics is to take sets of numbers and summarize them in meaningful ways. Here’s where you find out how to do that. We all know about averages and how to compute them. But that’s not the whole story. In this part, I tell you about additional statistics that fill in the gaps, and I show you how to use Excel to work with those statistics. I also introduce Excel graphics in this part.

Part 3: Drawing Conclusions from Data

Part 3 addresses the fundamental aim of statistical analysis: to go beyond the data and help decision-makers make decisions. Usually, the data are measurements of a sample taken from a large population. The goal is to use these data to figure out what’s going on in the population.

This opens a wide range of questions: What does an average mean? What does the difference between two averages mean? Are two things associated? These are only a few of the questions I address in Part 3, and I discuss the Excel functions and tools that help you answer them.

Part 4: Working with Probability

Probability is the basis for statistical analysis and decision-making. In Part 4, I tell you all about it. I show you how to apply probability, particularly in the area of modeling. Excel provides a rich set of built-in capabilities that help you understand and apply probability. Here’s where you find them.

Part 5: The Part of Tens

Part 5 meets two objectives. First, I get to stand on the soapbox and rant about statistical peeves and about helpful hints. The peeves and hints total up to ten. Also, I discuss ten (okay, 12) Excel things I couldn’t fit into any other chapter. They come from all over the world of statistics. If it’s Excel and statistical, and if you can’t find it anywhere else in the book, you’ll find it here.

As I said in the first three editions — pretty handy, this Part of Tens.

Appendix A: When Your Worksheet Is a Database

In addition to performing calculations, Excel serves another purpose: recordkeeping. Although it’s not a dedicated database, Excel does offer some database functions. Some of them are statistical in nature. I introduce Excel database functions in Appendix A, along with pivot tables that allow you to turn your database inside out and look at your data in different ways.

Appendix B: The Analysis of Covariance

The Analysis of Covariance (ANCOVA) is a statistical technique that combines two other techniques: analysis of variance and regression analysis. If you know how two variables are related, you can use that knowledge in some nifty ways, and this is one of the ways. The kicker is that Excel doesn’t have a built-in tool for ANCOVA — but I show you how to use what Excel does have so you can get the job done.

Bonus Appendix B1: When Your Data Live Elsewhere

This appendix is all about importing data into Excel — from the web, from databases, from text, and from PDF documents.

Bonus Appendix B2: Tips for Teachers (and Learners)

Excel is terrific for managing, manipulating, and analyzing data. It’s also a great tool for helping people understand statistical concepts. This appendix covers some ways for using Excel to do just that.

Samples and populations

On election night, TV commentators routinely predict the outcome of elections before the polls close. Most of the time they’re right. How do they do that?

The trick is to interview a sample of voters after they cast their ballots. Assuming the voters tell the truth about whom they voted for, and assuming the sample truly represents the population, network analysts use the sample data to generalize to the population of voters.

This is the job of a statistician — to use the findings from a sample to make a decision about the population from which the sample comes. But sometimes those decisions don’t turn out the way the numbers predicted. History buffs are probably familiar with the memorable picture of President Harry Truman holding up a copy of the Chicago Daily Tribune with the famous, but wrong, headline “Dewey Defeats Truman” after the 1948 election. Part of the statistician’s job is to express how much confidence he or she has in the decision.

Another election-related example speaks to the idea of the confidence in the decision. Pre-election polls (again, assuming a representative sample of voters) tell you the percentage of sampled voters who prefer each candidate. The polling organization adds how accurate it believes the polls are. When you hear a newscaster say something like “accurate to within 3 percent,” you’re hearing a judgment about confidence.

Here’s another example. Suppose you’ve been assigned to find the average reading speed of all fifth-grade children in the United States but you haven’t got the time or the money to test them all. What would you do?

Your best bet is to take a sample of fifth-graders, measure their reading speeds (in words per minute), and calculate the average of the reading speeds in the sample. You can then use the sample average as an estimate of the population average.

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: Testing Hypotheses.”

Here’s 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. When you confine your field of view to samples, your statistics are descriptive. When you broaden your horizons and concern yourself with populations, your statistics are inferential.

And here’s a notation convention you have to know: Statisticians use Greek letters (μ, σ, ρ) to stand for parameters, and English letters , s, r) to stand for statistics. Figure 1-1 summarizes the relationship between populations and samples, and parameters and statistics.

Variables: Dependent and independent

Simply put, a variable is something that can take on more than one value. (Something that can have only one value is called a constant.) Some variables you might be familiar with are today’s temperature, the Dow Jones Industrial Average, your age, and the value of the dollar against the euro.

Statisticians care about two kinds of variables: independent and dependent. Each kind of variable crops up in any study or experiment, and statisticians assess the relationship between them.

For example, imagine a new way of teaching reading that’s intended to increase the reading speed of fifth-graders. Before putting this new method into schools, it would be a good idea to test it. To do that, a researcher would randomly assign a sample of fifth-grade students to one of two groups: One group receives instruction via the new method, and 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 “Inferential Statistics: Testing Hypotheses.”

For now, understand that the independent variable here is Method of Instruction. The two possible values of this variable are New and Traditional. The dependent variable is reading speed — which you might measure in words per minute.

In general, the idea is to find out if changes in the independent variable are associated with changes in the dependent variable.

In the examples that appear throughout the book, I show you how to use Excel to calculate various characteristics of groups of scores. Keep in mind that each time I show you a group of scores, I’m really talking about the values of a dependent variable.

Types of data

Data come in four kinds. When you work with a variable, the way you work with it depends on what kind of data it is.

The first variety is called nominal data. If a number is a piece of nominal data, it’s just a name. Its value doesn’t signify anything. A good example is the number on an athlete’s jersey. It’s just a way of identifying the athlete and distinguishing him or her from teammates. The number doesn’t indicate the athlete’s level of skill.

Next come ordinal data. Ordinal data are all about order, and numbers begin to take on meaning over and above just being identifiers. A higher number indicates the presence of more of a particular attribute than a lower number. One example is the Mohs scale: Used since 1822, it’s a scale whose values are 1 through 10; mineralogists use this scale to rate the hardness of substances. Diamond, rated at 10, is the hardest. Talc, rated at 1, is the softest. A substance that has a given rating can scratch any substance that has a lower rating.

What’s missing from the Mohs scale (and from all ordinal data) is the idea of equal intervals and equal differences. The difference between a hardness of 10 and a hardness of 8 is not the same as the difference between a hardness of 6 and a hardness of 4.

Interval data provide 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. For ratio statements (twice as much as, half as much as) to be valid, zero has to mean the complete absence of the attribute you’re measuring. A temperature of 0 degrees F doesn’t mean the absence of heat — it’s just an arbitrary point on the Fahrenheit scale.

The last data type, ratio data, includes a meaningful zero point. For temperatures, the Kelvin scale gives ratio data. One hundred degrees Kelvin is twice as hot as 50 degrees Kelvin. This is because the Kelvin zero point is absolute zero, where all molecular motion (the basis of heat) stops. Another example is a ruler. Eight inches is twice as long as four inches. A length of zero means a complete absence of length.

Any of these data types can form the basis for an independent variable or a dependent variable. The analytical tools you use depend on the type of data you’re dealing with.

A little probability

When statisticians make decisions, they express their confidence about those decisions in terms of probability. They can never be certain about what they decide. They can only tell you how probable their conclusions are.

So what is probability? The best way to attack this is with a few examples. If you toss a coin, what’s the probability that it comes up heads? Intuitively, you know that if the coin is fair, you have a 50-50 chance of heads and a 50-50 chance of tails. In terms of the kinds of numbers associated with probability, that’s ½.

How about rolling a die? (That’s one member of a pair of dice.) What’s the probability that you roll a 3? Hmmm… . A die has six faces and one of them is 3, so that ought to be , right? Right.

Here’s one more. You have a standard deck of playing cards. You select one card at random. What’s the probability that it’s a club? Well … a deck of cards has four suits, so that answer is ¼.

I think you’re getting the picture. If you want to know the probability that an event occurs, figure out how many ways that event can happen and divide by the total number of events that can happen. In each of the three examples, the event we are 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 = = . What about the probability of rolling an even number? That has to be 2, 4, or 6, and the probability is (1 + 1 + 1)/6 = = .

On to another kind of probability question. Suppose you roll a die and toss a coin at the same time. What’s the probability you roll a 3 and the coin comes up heads? Consider all the possible events that could occur when you roll a die and toss a coin at the same time. Your outcome could be a head and 1-6 or a tail and 1-6. That’s a total of 12 possibilities. The head-and-3 combination can happen only one way, so the answer is .

In general, the formula for the probability that a particular event occurs is

I begin this section by saying that statisticians express their confidence about their decisions in terms of probability, which is really why I brought up this topic in the first place. This line of thinking leads me to conditional probability — the probability that an event occurs given that some other event occurs. For example, suppose I roll a die, take a look at it (so that you can’t see it), and tell you I’ve rolled an even number. What’s the probability that I’ve rolled a 2? Ordinarily, the probability of a 2 is , but I’ve narrowed the field. I’ve eliminated the three odd numbers (1, 3, and 5) as possibilities. In this case, only the three even numbers (2, 4, and 6) are possible, so now the probability of rolling a 2 is .

Exactly how does conditional probability play into statistical analysis? Read on.

Null and alternative hypotheses

Consider once again the coin-tossing study I mention in the preceding section. The sample data are the results from the 100 tosses. Before tossing the coin, you might start with the hypothesis that the coin is a fair one so that you expect an equal number of heads and tails. This starting point is called the null hypothesis. The statistical notation for the null hypothesis is H₀. According to this hypothesis, any heads-tails split in the data is consistent with a fair coin. Think of it as the idea that nothing in the results of the study is out of the ordinary.

An alternative hypothesis is possible — that the coin isn’t a fair one, and it’s loaded to produce an unequal number of heads and tails. This hypothesis says that any heads-tails split is consistent with an unfair coin. The alternative hypothesis is called, believe it or not, the alternative hypothesis. The statistical notation for the alternative hypothesis is H₁.

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 H₀. If the results are around 50 heads and 50 tails, don’t reject H₀.Similar ideas apply to the reading-speed example I give earlier, in the section “Samples and populations.” One sample of children receives reading instruction under a new method designed to increase reading speed, and the other learns via a traditional method. Measure the children’s reading speeds before and after instruction, and tabulate the improvement for each child. The null hypothesis, H₀, is that one method isn’t different from the other. If the improvements are greater with the new method than with the traditional method — so much greater that it’s unlikely that the methods aren’t different from one another — reject H₀. If they’re not greater, don’t reject H₀.

Notice that I didn’t say “accept H₀.” The way the logic works, you never accept a hypothesis. You either reject H₀ or don’t reject H₀.

Here’s a real-world example to help you understand this idea. When a defendant goes on trial, he or she is presumed innocent until proven guilty. Think of innocent as H₀. The prosecutor’s job is to convince the jury to reject H₀. If the jurors reject, the verdict is guilty. If they don’t reject, the verdict is not guilty. The verdict is never innocent. That would be like accepting H₀.

Back to the coin-tossing example. Remember I said “around 50 heads and 50 tails” is what you could expect from 100 tosses of a fair coin. What does around mean? Also, I said if it’s 90-10, reject H₀. What about 85-15? 80-20? 70-30? Exactly how much different from 50-50 does the split have to be for you reject H₀? In the reading-speed example, how much greater does the improvement have to be to reject H₀?

I won’t answer these questions now. Statisticians have formulated decision rules for situations like this, and you explore those rules throughout the book.

Two types of error

Whenever you evaluate the data from a study and decide to reject H₀ or to not reject H₀, you can never be absolutely sure. You never really know what the true state of the world is. In the context of the coin-tossing example, that means you never know for certain if the coin is fair or not. All you can do is make a decision based on the sample data you gather. If you want to be certain about the coin, you’d have to have the data for the entire population of tosses — which means you’d have to keep tossing the coin until the end of time.

Because you’re never certain about your decisions, it’s possible to make an error regardless of what you decide. As I mention earlier in this chapter, the coin could be fair and you just happen to get 99 heads in 100 tosses. That’s not likely, and that’s why you reject H₀. It’s also possible that the coin is biased, yet you just happen to toss 50 heads in 100 tosses. Again, that’s not likely and you don’t reject H₀ in that case.

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 H₀ 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 H₀ and you should have, that’s a Type II error. That happens if you don’t reject the hypothesis that the coin is fair and in reality it’s biased.

How do you know if you’ve made either type of error? You don’t — at least not right after you make your decision to reject or not reject H₀. (If it’s possible to know, you wouldn’t make the error in the first place!) All you can do is gather more data and see if the additional data are consistent with your decision.

If you think of H₀ as a tendency to maintain the status quo and not interpret anything as being out of the ordinary (no matter how it looks), a Type II error means you missed out on something big. Looked at in that way, Type II errors form the basis of many historical ironies.

Here’s what I mean: In the 1950s, a particular TV show gave talented young entertainers a few minutes to perform on stage and a chance to compete for a prize. The audience voted to determine the winner. The producers held auditions around the country to find people for the show. Many years after the show went off the air, the producer was interviewed. The interviewer asked him if he had ever turned down anyone at an audition whom he shouldn’t have.

“Well,” said the producer, “once a young singer auditioned for us and he seemed really odd.”

“In what way?” asked the interviewer.

“In a couple of ways,” said the producer. “He sang really loud, gyrated his body and his legs when he played the guitar, and he had these long sideburns. We figured this kid would never make it in show business, so we thanked him for showing up, but we sent him on his way.”

“Wait a minute — are you telling me you turned down …?”

“That’s right. We actually said no … to Elvis Presley!”

Now that’s a Type II error.