Instructions for
Calculating Descriptive Statistics in Excel
(1) Go to Tools—Addins in menu, and make sure that the box in front of Analysis ToolPak is checked.
(2) Go to Tools—Data Analysis—Descriptive Statistics in menu.
(3) You will have to
indicate the
Check the box in front of Summary Statistics.
Optional:
(b) If you would like to see the confidence
interval about the mean, check the box in front of Confidence Level for Mean. [The default is 95%.]
You have to indicate where you want the output to
appear. You will probably want the
output to appear either on the same page or on another worksheet. If you want the output to appear on the same
page, then check the circle in front of
Example: A manager wants to know how long it takes to assemble a laptop computer. She randomly selects 14 employees.
Times were (in minutes):
100, 90, 45, 67, 80, 92, 70, 71, 77, 29, 89, 76, 80, 83
The data was input into cells b4 to b17 (there are 14 scores).
Here is the Excel output:
Column1 



Mean 
74.92857143 
Standard
Error 
5.013678308 
Median 
78.5 
Mode 
80 
Standard
Deviation 
18.75946647 
Sample
Variance 
351.9175824 
Kurtosis 
1.923164749 
Skewness 
1.31355395 
Range 
71 
Minimum 
29 
Maximum 
100 
Sum 
1049 
Count 
14 
Confidence
Level(95.0%) 
10.83139138 
(a) Mean,
X, (average time) is 74.93 minutes (rounded).
(b) Standard Error is
5.014 (rounded). To calculate it: divide
the standard deviation by the square root of the sample size. This measure will be used in the second part
of the course dealing with inference. You can ignore it for now.
(c) Median is 78.5 minutes; half the employees did better than this and half did worse.
(d) Mode is 80 minutes; two employees did the job in this time. All other scores had frequencies of 1.
(e) Standard Deviation is a measure of dispersion. The standard deviation is 18.76 minutes (rounded). If the scores are normally distributed, then about 95.5% of scores should be within two standard deviations of the mean (see below). Another way of expressing this is that 95.5% of individual scores should be between the mean ± two standard deviations (74.93 ± 37.52), or, 37.41 minutes and 112.45 minutes.
(f) Sample Variance is the standard deviation squared, 351.97 (rounded).
(g) Kurtosis is a measure of peakedness and is rarely used.
(h) Skewness value is 1.31. If the data is symmetric, the value should be about 0. There is a negative skew to this data set: the mean is below the median.
(i) Range is 71 minutes. The maximum value – minimum value, or 100 minutes – 29 minutes = 71 minutes
(j) Minimum (lowest value) is 29 minutes. One employee was very efficient and did the job in 29 minutes.
(k) Maximum (highest value) is 100 minutes. This employee was relatively slow and did the job in 100 minutes.
(l) Sum is the sum of all 14 observations, åX_{i}.
(m) Count is the sample size.
(n) Confidence Level. If you wish to construct a 95% confidence interval about the mean, you use this value. As you know, you are looking at a sample of 14 individuals and the measure you obtained is a statistic, i.e., the sample mean, X. If you wish to construct an interval that you are 95% certain contains the population parameter, m (mu), you calculate
X ± t_{13} (standard error). This is something you will learn in the second half of the course. The tdistribution is used for small samples when the population standard deviation is unknown.