XL: Statistical Functions Are Different from Descriptive Statistics

ID: Q179121

The information in this article applies to:

Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c
Microsoft Excel 97 for Windows

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).

Additional query words: XL5 XL7 XL97 Variance Probability

Keywords : xlformula
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0a,5.0c,7.0
Platform : MACINTOSH WINDOWS
Issue type :