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

 Last reviewed: November 30, 1994
Article ID: Q92558
The information in this article applies to:
  • Microsoft Excel for Windows, version 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)
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.

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

KBCategory: kbprb
KBSubcategory:

Additional reference words: 4.00 4.00a PTTESTM analysis tool pack pak



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.