Excel: T-Test May Return Negative Pooled Variance for Means

ID: Q92558


The information in this article applies to:


SYMPTOMS

In Microsoft Excel, if you use the t-Test tool from the Analysis ToolPak to compute a paired two-sample, t-Test may return a negative value for the pooled variance. This result is incorrect because the pooled variance, by definition, cannot be negative.


STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. This problem was corrected and does not occur in later versions of Microsoft Excel.


MORE INFORMATION

On page 46 of the version 4.0 "Microsoft Excel User's Guide 2," the example for the t-Test: Paired Two-Sample for Means section shows a negative value in the Pooled Variance cell. By definition, the formula for the pooled variance (sometimes called the pooled estimator) cannot be a negative value.

Microsoft Excel uses the following formula to calculate pooled variance


   (((N1-1) * s1^2) + ((N2-1) * s2^2)) / (N1+N2-2) 


where:


REFERENCES

"Microsoft Excel User's Guide 2," version 4.0, pages 45-46

"Probability and Statistics for Engineering and the Sciences," 3d ed., Jay L. Devore, Brooks/Cole Publishing Company, 1991

Additional query words: 4.00a PTTESTM analysis tool pack pak


Keywords          : 
Version           : 
Platform          : 
Issue type        : 

Last Reviewed: March 29, 1999