Microsoft KB Archive/179121

= Statistical Functions Are Different from Descriptive Statistics =

Article ID: 179121

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



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( , - 1)*STDEV/SQRT()   CONFIDENCE: =NORMSINV(1- /2)*STDEVP/SQRT() NOTE: is the worksheet address of the numerical array you are tesing. is typically 5 percent, and  is the number of samples.

