- Descriptive statistics for data using Data Analysis Add-in

- Statistics using individual function commands.

**DESCRIPTIVE STATISTICS USING DATA ANALYSIS ADD-IN
**

We consider the following gasoline price data (cents per gallon)

These data are in gasolinepricedata.csv

[Alternatively type in cells A1:A10 the ten values

117.9, 119.9, 115.9, 121.9, 121.9, 118.9, 118.9, 123.9, 125.9, 113.9.

and then insert a header GAS PRICE in new row 1] .

To obtain various descriptive statistics

- Choose the Data Tab and the Analysis Group and Click on Data
Analysis.

[Note: if the Data Analysis group is not there then see Excel 2007: Access and Activating the Data Analysis Add-in]

- In the menu list highlight Descriptive Statistics.

Clicking OK yields the dialog box

- Choose input range as A1:A11 (by selecting this range with the mouse or by typing this in)
- Select labels in First rows (assuming labels have been provided)
- For output range choose e.g. cell C1 (choose a location where two adjacent columns have no data)
- Select summary statistics.
- Select confidence Level for mean (explained later under
statistical inference).

Selecting OK yields the output

An explanation of the kwy parts of this output is the following:

Mean | 119.9 | ybar = the sample mean. |

Standard error | 1.145038 | s/sqrt(n) = the estimated standard deviation of the sample mean |

Standard deviation | 3.620927 | s = the estimated standard deviation of y |

Variance | 13.11111 | s-squared = the estimated variance of y = Sum of (y_i - ybar)^2 |

Count | 10 | n = sample size |

Confidence level (95%) | 2.590255 | t(.025;n-1)*s/sqrt(n) |

The mean is the simple average of the observations and is the
standard estimate of the center of the data. (Other estimates are the
median and the
mode).

The standard deviation is the standard estimate of the variability of
the
data.

The standard error and the confidence level are used for statistical
inference
on the population mean and are discussed in Excel: Statistical
Inference for univariate data

**ADDITIONAL SUMMARY STATISTICS USING DATA ANALYSIS**

The standard output gives Mean, Standard error, Median, Mean,
Standard deviation, Variance, Kurtosis, Skewness, Range, Maximum,
Minimum,

Sum, and Count.

Additionally one can obtain the k-th largest and k-th smallest
values by
checking the appropriate column and setting k.

For example, with 43 observations
one might choose the 11th smallest and 11th largest to obtain
approximate
estimates of the lower quartile and upper quartile. (Easier is to
separately
use the Quartile or Percentile Commands).

**STATISTICS BY INDIVIDUAL COMMAND**

The descriptive statistics can be obtained separately by individual
commands.

In addition one can find many more commands than appears in summary
statistics.

Assume the data is in cells A2:A11. To obtain the **sample mean**
and
report it in, say, cell A13

**either** type in cell A13 the text = AVERAGE(A2:A11)

**or** do the following:

- Click on cell A13 (this is where the result will be stored).
- Select the Formulas Tab and the Function Library group
- Click on Autosum
- Choose function name AVERAGE
- Enter A2:A11.

The following commands produce the same output as the default output for Tools | Data Analysis

- Mean: = AVERAGE(A2:A11)
- Standard error: = STDEV(A2:A11) / SQRT(COUNT(A2:A11))\
- Median: = MEDIAN(A2:A11)
- Mean: = MODE(A2:A11)
- Standard deviation: = STDEV(A2:A11)
- Variance: = VAR(A2:A11)
- Kurtosis: = KURT(A2:A11)
- Skewness: = SKEW(A2:A11)
- Range: = MAX(A2:A11) - MIN(A2:A11)
- Maximum: = MAX(A2:A11)
- Minimum: = MIN(A2:A11)
- Sum: = SUM(A2:A11)
- Count: = COUNT(A2:A11)

The complete Excel 2007 statistical commands (in Formulas Tab |
Function Library Group | More Functions | Statistical) are:

- AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS
- BETADIST, BETAINV, BINOMDIST
- CHIDIST, CHIINV, CHITEST, CONFIDENCE, CORREL, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, COVAR, CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST, FINV, FISHER, FISHERINV, FORECAST, FREQUENCY, FTEST
- GAMMADIST, GAMMAINV, GAMMLN, GEOMEAN, GROWTH
- HARMEAN, HYPGEOMDIST
- INTERCEPT
- KURT
- LARGE, LINEST, LOGEST, LOGINV, LOGNORMDIST
- MAX, MAXA, MEDIAN, MIN, MINA, MODE
- NEGBINOMDIST, NORMDIST, NORMINV, NORMSDIST, NORMSINV
- PEARSON, PERCENTILE, PRECENTRANK, PERMUT, POISSON, PROB
- QUARTILE
- RANK, RSQ
- SKEW, SLOPE, SMALL, STANDARDIZE, STDEV, STDEVA, STDEVP, STDEVPA, STEYX
- TDIST, TINV, TREND, TRIMMEAN, TTEST
- VAR, VARA, VARP, VARPA
- WEIBULL
- ZTEST.

For further information on how to use Excel go to

http://cameron.econ.ucdavis.edu/excel/excel.html