Microsoft KB Archive/111266

= Microsoft Knowledge Base =

Combining CHITEST with CHIINV to Determine Critical Value
Last reviewed: June 19, 1997

Article ID: Q111266

The information in this article applies to:


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

SUMMARY
CHITEST is a statistical function that mathematically determines the relationship of probability distributions between multiple data sets. The process involves determining a "critical value" and a probability that the results exceed that value.

MORE INFORMATION
A lot of statistical analysis is based on the assumption of normal distributions of data. Given normal distributions, certain assumptions can then be made based on mathematical evaluation.

CHITEST, pronounced "kye-test", is a means by which data can be evaluated foregoing the assumption of normal data distributions. The CHITEST function can be used in various scenarios where it is important to determine the relationships of multiple data samples. This function mathematically determines the relationship of data based upon expected results compared with actual results. Two Microsoft Excel functions are involved in this process: CHITEST and CHIINV. CHITEST returns the probability, or alpha value, associated with determining the accuracy of the relationship. CHIINV returns the critical value that will mathematically compare the actual data results with expected values.

From this data, two hypothesis are developed: a null hypothesis (Ho), indicating there is no dependence or relativity to the data; and an alternative hypothesis (Ha), indicating that there is a dependence or predictable relationship in the data. If actual results (manually calculated Chi-squared statistic) exceed expected results, calculated critical value, it is concluded that the null hypothesis (no significant relationship) is true.

The following is a manual calculation of the example shown on page 53 of the "Function Reference" version 4.0.

Men       Women ---

Actual   58         35             93 Expected 45.35185   47.64815

Actual   11         25             36 Expected 17.55556   18.44444

Actual   10         23             33 Expected 16.09259   16.90741

79        83             162 The actual results are first calculated by creating a conditional data table illustrated here. The ACTUAL data is first summed horizontally and vertically. The EXPECTED data is then calculated by multiplying the actual row total by the actual column total and dividing by the grand total. For the first example, the expected value of 45.35185 is calculated by multiplying 93*79/162. Each subsequent expected value is calculated. The actual chi-squared result is then calculated by summing the squared differences divided by the expected value. The chi-squared statistic for the first example is (58-45.35185)^2/45.351852. A total of these factors represents the actual chi-squared statistic for the sample.

The actual totals for the above example are given below:

3.527433722  3.357436916   2.447960619   2.329986613   2.306631718   2.195468744   16.16491833  Actual Chi-squared statistic The formula for Microsoft Excel CHITEST critical value is:

=CHIINV(CHITEST(actual_range, expected_range),2) Calculating this in Microsoft Excel returns a critical value of 10.1064. Because the actual statistic exceeds the critical value, it is concluded that the null hypothesis (no significant relationship) is true.