CentralLimThm.XLS
Demonstrating the Central Limit Theorem
on a Microsoft Excel Spreadsheet
by John O. McClain Links: [ Free Software | John McClain's Page | Errors
] 29 October 2007
The Central Limit Theorem (CLT) is the basis for much of what we do in Statistics. Simply stated, it says that we can use the Normal Probability Distribution when we are working with Sample Averages, even when the data from which the average was calculated does not have the Normal Distribution. The assumptions are that
(1) the observations are
independent of one another (which depends on how the data set was gathered),
(2) the observations come from a probability distribution for which the mean
and standard deviation exist (almost always satisfied), and
(3) the sample is "sufficiently large".
This Excel file allows you to experiment with the third assumption, to see just how many observations must be in a data set for the CLT to hold, and how that depends on the shape of the underlying probability distribution. It allows you to change the type of distribution (uniform or gamma), the mean, standard deviation and sample size.
The following example uses the Uniform probability distribution, which has a rectangular shape, rather than a bell shape.
Figure 1 (below) shows the result after one sample of 5 observations. The top diagram shows the distribution of the 5 observations, and the bottom diagram shows the average. There is only 1 bar in the bottom diagram because only 1 sample has been observed, so there is only one average to plot.
Figure 2 (farther below) shows the results after 521 samples of 5 each. The top diagram shows the distribution of the 5x521 = 2605 individual observations, and the bottom diagram shows the distribution of the 521 averages. Notice the bell-shaped distribution of the averages, despite the nearly-flat distribution of the individual data points.
No matter what distribution you choose, the second figure looks bell-shaped. As you increase the sample size, that picture of the distribution of the sample means gets closer and closer to the normal distribution, which is what the Central Limit Theorem predicts. Many books refer to “n = 30” as the necessary sample size for the CLT to apply. As you can see (and you can test with this file) a much smaller sample is often sufficient!
Sampling is done with a macro. To use this macro, you must enable macros when you open the file (see below).
This is a very simple program, intended for teaching purposes. It includes three pages of instructions, with pictures. You are welcome to use it in any manner, and change it as you see fit. The program comes without any guarantee whatsoever, and is distributed free of charge.
Click Here to get a free copy of the Microsoft Excel Spreadsheet.
Figure 1 (below): The CentralLimThm spreadsheet after running a single sample,
having 5 observations.
Figure 2 (below):
Same setup after 521 samples of 5 observations each.
If it Doesn't Work:
Some users find that the buttons do not work.
If this happens, your file contains a sheet named “Warning” on
which you will find instructions about what to do.
Back to: [ Free Software | John McClain's Page ]