Descriptive statistics and confidence interval from Excel
Excel is a versatile tool for data management and analysis. The relevant tool for statistical data analysis is the Data Analysis tool in Excel. This tool provides access to a wide range of valuable statistical tests and graphing utilities. Among them are analysis of variance, correlation, histogram, and the t-test. This guide provides a graphical illustration of how descriptive statistics and confidence intervals on the mean can be computed and constructed using the Descriptive Statistics utility under the Data Analysis tool in Excel. This guide was prepared using Excel 2007, which has a quite different interface from the older versions of Excel.
Nada Milk Filling Operation
At Al-Othman Agricultural Production and Processing Co, Nada long-life, whole milk is the main product for the company, due to competition and high production costs. The industrial engineer is concerned with the milk filling machine; the machine has been in service for a long time and it might produce products with volume less or more than the target volume, which is set at one liter. He took a random sample of 20 products and took them to the lab for analysis, where the volume of each product was measured. The product and the the volume in liters are shown in the table below.
| Observation | Liters |
| 1 | 0.9903 |
| 2 | 0.9965 |
| 3 | 1.0003 |
| 4 | 1.0018 |
| 5 | 1.002 |
| 6 | 0.9935 |
| 7 | 0.9938 |
| 8 | 0.9917 |
| 9 | 1.0027 |
| 10 | 1.0001 |
| 11 | 0.9906 |
| 12 | 1.0016 |
| 13 | 1.0086 |
| 14 | 1.0006 |
| 15 | 1.0029 |
| 16 | 1.0049 |
| 17 | 1.0043 |
| 18 | 0.9917 |
| 19 | 1.0078 |
| 20 | 0.9988 |
He wants now to use Excel to compute some essential statistics and construct a confidence interval on the mean volume.
Steps of the computation of descriptive statistics and construction of the confidence interval in Excel
The first step will be to enter the data to Excel. The data can be entered either in a column or row.
The essential Excel tools for data management are stored under the Data tab.
The Data Analysis tool is located under the Data tab:
Clicking on Data Analysis will open the Data Analysis dialog box:
Data Analysis tool has 19 statistical and graphical tools. For descriptive statistics, the Descriptive Statistics tool is used. Inside the Descriptive Statistics dialog box, the cell range of the data is entered into the input range field, using the procedure of click and drag in Excel. Under the Grouped By section, it should be indicated how the data are laid out in the worksheet; in column or row.
Under the Output Options, Summary statistics and Confidence level for mean should be selected. The default confidence level can be changed to any other value.
When OK is clicked, Excel creates a new worksheet that shows the descriptive statistics computed from the data as well as the confidence interval on the mean, assuming the population variance is not known.
To find the confidence interval, the value of the confidence level is added and subtracted from the mean. For the milk filling operation, the 95% confidence interval is [0.9966, 1.0019], rounded to the fourth decimal.
Dr Muhammad Al-Salamah
First published in 2008