Excel offers more worksheet functions that pertain to the normal distribution than to any other. This chapter explains what a normal distribution is and how to use those Excel functions that map it.
About the Normal Distribution
You cannot go through life without encountering the normal distribution, or “bell curve,” on an almost daily basis. It’s the foundation for grading “on the curve” when you were in elementary and high school. The height and weight of people in your family, in your neighborhood, in your country each follow a normal curve. The number of times a fair coin comes up heads in ten flips follows a normal curve. The title of a contentious and controversial book published in the 1990s. Even that ridiculously abbreviated list is remarkable for a phenomenon that was only starting to be perceived 300 years ago.
The normal distribution occupies a special niche in the theory of statistics and probability, and that’s a principal reason Excel offers more worksheet functions that pertain to the normal distribution than to any other, such as the t, the binomial, the Poisson, and so on. Another reason Excel pays so much attention to the normal distribution is that so many variables that interest researchers—in addition to the few just mentioned—follow a normal distribution.
Characteristics of the Normal Distribution
There isn’t just one normal distribution, but an infinite number. Despite the fact that there are so many of them, you never encounter one in nature.
Those are not contradictory statements. There is a normal curve—or, if you prefer, normal distribution or bell curve or Gaussian curve—for every number, because the normal curve can have any mean and any standard deviation. A normal curve can have a mean of 100 and a standard deviation of 16, or a mean of 54.3 and a standard deviation of 10. It all depends on the variable you’re measuring.
The reason you never see a normal distribution in nature is that nature is messy. You see a huge number of variables whose distributions follow a normal distribution very closely. But the normal distribution is the result of an equation, and can therefore be drawn precisely. If you attempt to emulate a normal curve by charting the number of people whose height is 56″, all those whose height is 57″, and so on, you will start seeing a distribution that resembles a normal curve when you get to somewhere around 30 people.
As your sample gets into the hundreds, you’ll find that the frequency distribution looks pretty normal—not quite, but nearly. As you get into the thousands you’ll find your frequency distribution is not visually distinguishable from a normal curve. But if you apply the functions for skewness and kurtosis discussed in this chapter, you’ll find that your curve just misses being perfectly normal. You have tiny amounts of sampling error to contend with, for one; for another, your measures won’t be perfectly accurate.
A normal distribution is not skewed to the left or the right but is symmetric. A skewed distribution has values whose frequencies bunch up in one tail and stretch out in the other tail.
Skewness and Standard Deviations
The asymmetry in a skewed distribution causes the meaning of a standard deviation to differ from its meaning in a symmetric distribution, such as the normal curve or the t-distribution (see Chapters 8 and 9, for information on the t-distribution). In a symmetric distribution such as the normal, close to 34% of the area under the curve falls between the mean and one standard deviation below the mean. Because the distribution is symmetric, an additional 34% of the area also falls between the mean and one standard deviation above the mean.
But the asymmetry in a skewed distribution causes the equal percentages in a symmetric distribution to become unequal. For example, in a distribution that skews right you might find 45% of the area under the curve between the mean and one standard deviation below the mean; another 25% might be between the mean and one standard deviation above it.
In that case, you still have about 68% of the area under the curve between one standard deviation below and one standard deviation above the mean. But that 68% is split so that its bulk is primarily below the mean.
The figure at right shows several distributions with different degrees of skewness. A curve is said to be skewed in the direction that it tails off: The log X curve is “skewed left” or “skewed negative.”
The normal curve shown in the figure above(based on a random sample of 5,000 numbers, generated by Excel’s Data Analysis add-in) is not the idealized normal curve but a close approximation. Its skewness, calculated by Excel’s SKEW() function, is -0.02. That’s very close to zero; a purely normal curve has a skewness of exactly 0.
The X2 and log X curves in the figure above are based on the same X values as form the figure’s normal distribution. The X2 curve tails to the right and skews positively at 0.57. The log X curve tails to the left and skews negatively at -0.74. It’s generally true that a negative skewness measure indicates a distribution that tails off left, and a positive skewness measure tails off right.
The F curve in the figure aboveis based on a true F-distribution with 4 and 100 degrees of freedom. (This book has much more to say about F-distributions beginning in Chapter 10, “Testing Differences Between Means: The Analysis of Variance.” An F-distribution is based on the ratio of two variances, each of which has a particular number of degrees of freedom.) F-distributions always skew right. It is included here so that you can compare it with another important distribution, t, which appears in the next section on a curve’s kurtosis.
Several methods are used to calculate the skewness of a set of numbers. Although the values they return are close to one another, no two methods yield exactly the same result. Unfortunately, no real consensus has formed on one method. I mention most of them here so that you’ll be aware of the lack of consensus. More researchers report some measure of skewness than was once the case, to help the consumers of that research better understand the nature of the data under study. It’s much more effective to report a measure of skewness than to print a chart in a journal and expect the reader to decide how far the distribution departs from the normal. That departure can affect everything from the meaning of correlation coefficients to whether inferential tests have any meaning with the data in question.
For example, one measure of skewness proposed by Karl Pearson (of the Pearson correlation coefficient) is shown here:
- Skewness = (Mean – Mode) / Standard Deviation
But it’s more typical to use the sum of the cubed z-scores in the distribution to calculate its skewness. One such method calculates skewness as follows: