Statistical Functions Are Different from Descriptive Statistics (179121)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
This article was previously published under Q179121 SYMPTOMS
The results returned by the CONFIDENCE function do not always agree with
the results returned by Confidence Level (click Descriptive Statistics in
the Data Analysis dialog box).
MORE INFORMATION
When you calculate statistics for an array of numbers, you can calculate
several descriptive measurements, including the following:
- Number of samples (n)
- Average or mean (mu)
- Standard deviation (sigma)
NOTE: To calculate the mean, you must know the range of values in which
the average is likely to fall with a certain level of confidence (1 -
alpha). Alpha is typically 5 percent (0.05); therefore, the confidence
level (1 - alpha) is typically 95 percent. The value n is synonymous with
Count (see the table that follows).
When you are working with an array of n numbers, you can either use the
Descriptive Statistics command that is listed in the Data Analysis dialog
box, which is available if the Analysis Tool Pack (ATP) add-in is loaded,
or you can use the various worksheet functions for these statistics.
However, note that the two methods use different formulas when they
calculate the statistics.
Equivalent worksheet functions for the statistical calculations are listed
in the following table.
Descriptive statistics
(Sample Values) Excel functions
--------------------------------------------
Mean AVERAGE
Standard Error STDEV, SQRT(Count)
Median MEDIAN
Mode MODE
Standard Deviation STDEV or STDEVA
Sample Variance VAR
Kurtosis KURT
Skewness SKEW
Range MAX, MIN
Minimum MIN
Maximum MAX
Sum SUM
Count COUNT or COUNTA
Confidence Level(95.0%) (None)
Note that the output for Descriptive Statistics is labeled "Sample
Values." This is a meaningful label. This label means that these values
are calculated based on the sample values as part of a larger population;
therefore, the values are not necessarily representative of the
population. This makes a difference in the calculations for the Standard
Deviation, the Standard Error, and the Confidence (Level).
The corresponding functions for the Standard Deviation, based on a sample
that is the entire population or that is representative of the entire
populate, are STDEVP or STDEVPA.
Confidence Function
The CONFIDENCE function calculations are based on a sample that is
representative of the entire population or that is the entire population
itself. This does not agree with the Confidence Level calculation in the
Descriptive Statistics package that is available in the Analysis Tool
Pack.
The Confidence Level calculation in the Descriptive Statistics package
uses the Student's t function to calculate the confidence, while the
CONFIDENCE worksheet function uses the standardized Normal curve to perform
the calculation. In terms of other worksheet functions, the two
calculations are performed as follows.
Confidence Level:
=TINV(<alpha>,<n> - 1)*STDEV(<array>)/SQRT(<n>)
CONFIDENCE:
=NORMSINV(1-<alpha>/2)*STDEVP(<array>)/SQRT(<n>)
NOTE: <array> is the worksheet address of the numerical array you are
tesing. <alpha> is typically 5 percent, and <n> is the number of samples.
REFERENCES
"Basic Statistics," Boris Parl, Doubleday & Company Inc., Garden City,
New York, 1967.
"Dictionary/Outline of Basic Statistics," John E. Freund and Frank J.
Williams, McGraw-Hill, New York, 1966.
"CRC Handbook of tables for Probability and Statistics," William H. Beyer,
Ed., The Chemical Rubber Co., Cleveland, Ohio, 1966.
"Microsoft Excel 97 Worksheet Function Reference," Maureen Williams
Zimmerman, Project Editor, Microsoft Press, Redmond WA, 1997 (This is a
reprint of the Microsoft Excel 1997 Help file for worksheet functions).
Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
---|
Keywords: | KB179121 |
---|
|