XL: Statistical Functions Different from Descriptive Statistics

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

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)

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

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