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

• Microsoft Excel for Windows, versions 4.0, 4.0a
• Microsoft Excel for the Macintosh, version 4.0

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) 

• N1 is the number of data points in Group1.



• N2 is the number of data points in Group2.



• S1 is the standard deviation of Group1.



• S2 is the standard deviation of Group2.

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