Microsoft KB Archive/92558

{|
 * width="100%"|

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

 * }

-

The information in this article applies to:


 * 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 &quot;Microsoft Excel User's Guide 2,&quot; 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:


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