Problem
You need to compute summary statistics (measures of central tendency and dispersion) but are not sure what support Excel offers for these calculations.
Solution
Excel offers many builtin spreadsheet functions allowing you to quickly compute summary statistics. Further, the Analysis ToolPak offers a Descriptive Statistics tool . You can perform the same statistical calculations offered in the Descriptive Statistics tool using spreadsheet functions. There are, however, caveats that you need to know about. Read the following discussion for more information.
Discussion
I'll show you how to perform basic statistical calculations in Excel on an example set of data. The dataset I'll use was downloaded from the National Institute of Standards and Technology statistical reference dataset page at http://www.itl.nist.gov/div898/strd/general/dataarchive.html. The dataset consists of speed of light measurements from a study conducted by Michelson in 1879. The data consists of 100 values representing the speed of light in units of millions of meters per second. Figure 51 illustrates a portion of this data along with several computed statistics for the dataset.
The dataset is in column B, labeled Speed of Light (millions of m/s). There are 100 values in this column, but only the first dozen and a half or so are shown in the figure.
Column F contains statistics for this dataset computed using the Analysis ToolPak's Descriptive Statistics tool. The corresponding labels contained in column E indicate what each value represents. Column H contains statistics computed using Excel's builtin spreadsheet formulas. I'll show you how to use both methods.
First, to use the Descriptive Statistics tool, you have to open the Analysis ToolPak by selecting Tools images/U2192.jpg border=0> Data Analysis from the main menu bar. This action opens the Data Analysis dialog shown in Figure 52.
Select Descriptive Statistics from the list of available tools and press OK to open the Descriptive Statistics dialog box shown in Figure 53.
In the Input Range field, type (or select directly from your spreadsheet) the cell range containing the data you want to analyze. Since the example data is in a column, I selected the Grouped By: Columns option.
For this example, I decided to place the output on the same spreadsheet, next to the dataset. Therefore, I specified cell E6 for the Output Range. This specified cell represents the upperleft cell of the output table. You may choose to place the output on a new worksheet, in which case you need to specify a worksheet name.
Figure 51. Summary statistics of speed of light measurements
Figure 52. Data Analysis dialog box
I checked all of the options shown in the lower part of the dialog box in Figure 53 in order to generate all the available statistics. Pressing OK generates the results shown earlier in Figure 51.
Figure 53. Descriptive Statistics dialog box
You can compute the same statistics using only Excel's builtin functions. Cells H8 through H23 in Figure 51 contain the same set of statistics, computed using Excel's functions. Table 51 shows the cell formula I used to compute each statistic.
Statistic 
Cell formula 

Mean 
=AVERAGE(Data) 
Standard Error 
=H12/SQRT(H20) 
Median 
=MEDIAN(Data) 
Mode 
=MODE(Data) 
Standard Deviation 
=STDEV(Data) 
Sample Variance 
=VAR(Data) 
Kurtosis 
=KURT(Data) 
Skewness 
=SKEW(Data) 
Range 
=h18H17 
Minimum 
=MIN(Data) 
Maximum 
=MAX(Data) 
Sum 
=SUM(Data) 
Count 
=COUNT(Data) 
Largest(1) 
=LARGE(Data,1) 
Smallest(1) 
=SMALL(Data,1) 
Confidence Level(95.0%) 
=CONFIDENCE(0.05,H12,100) 
Most of these formulas require the cell range containing the input data as an argument. For convenience, I named the cell range B5 to B104 (containing the input data) Data. (See Recipe 1.14 to learn how to name cells and cell ranges.)
Table 51 illustrates that Excel has builtin formulas for all of these basic statistics with two exceptions. There's no formula for computing the Standard Error (standard error of the mean in this case). Nor is there a formula for computing the Range. However these two statistics are easily computed using the results of other formulas. For example, to compute the range you simply take the difference between the Maximum and the Minimum. To compute the Standard Error of the mean, you simply divide the Standard Deviation by the square root of Count.
If you take a close look at Figure 51, you'll see that the statistics computed using Excel's formulas exactly match those computed with the Analysis ToolPak with one exception: the values for the 95% confidence interval are different. This difference is due to the fact that the CONFIDENCE function uses the inverse of a standard normal distribution to compute the confidence interval, whereas the Analysis ToolPak uses the inverse of Student's tdistribution. Excel does provide other functions that allow you to compute confidence intervals using Student's tdistribution. See Recipes 5.3 and 8.8 for more information.
I should also mention that some of Excel's statistics functions (including those in the Analysis ToolPak) have historically dealt poorly with some troublesome datasets. For example, if your dataset contained very large numbers with very small differences between them, the Descriptive Statistics tool would compute the standard deviation and variance incorrectly, and this would then affect results of other statistics that used those values. The workaround in this case is to center your data by subtracting the mean from each value (or by shifting the data by a sufficiently large value), and then compute the standard deviation and variance based on the centered data.
In Excel 2003, it appears that the statistics functions have been fixed, so this error no longer occurs. To see if this error exists on your version of Excel, enter the following data in a spreadsheet and compute the standard deviation: 10000000001, 10000000002, 10000000003, 10000000004, and 10000000005. The average, standard deviation, and variance for this dataset are 10000000003, 1.58113883, and 2.5, respectively. In older versions of Excel, the standard deviation and variance would come out to 0.
If you center your data by subtracting the mean from each value, your dataset should look like this: 2, 1, 0, 1, 2. The standard deviation and variance should be the same as for the uncentered dataset. In older versions of Excel, the standard deviation and variance for this centered dataset should come out correctly.
If you shift the original dataset by subtracting, say, 1e10 from each value, you end up with a dataset that looks like this: 1, 2, 3, 4, 5. The standard deviation and variance should be the same as in the previous two cases. Again, older versions of Excel deal with this shifted dataset just fine and compute the correct statistics.

See Also
Excel's help guide includes a complete list of all its builtin statistical functions. You can find this list by searching Excel's help for the topic "Statistical Functions."
Excel also has several builtin summation functions that are very handy for statistical calculations that involve sums, sums of squares, sums of squared differences, and so on. These functions are discussed in Recipe 7.1.
Moreover, Excel has builtin functions to support regression and curve fitting. These, along with other regression techniques, are discussed in Chapter 8.
Using Excel
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Charting
Statistical Analysis
Time Series Analysis
Mathematical Functions
Curve Fitting and Regression
Solving Equations
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations
Index