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.
Additional query words: 4.00 Last reviewed: June 19, 1997 |
