O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

5.1. Computing Summary Statistics

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 built-in 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 5-1 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required