The CHITEST function returns the value from the chi-square distribution for the given data. All values are number values.
CHITEST(actual-values, expected-values)
actual-values: The collection containing the actual values.
expected-values: The collection containing the expected values.
Notes
The degrees of freedom relating to the value returned is the number of rows in actual-values minus 1.
Each expected value is computed by multiplying the sum of the row by the sum of the column and dividing by the grand total.
Example |
|---|
Suppose that for a particular question on a survey, the range of possible answers was 1 to 5. The survey results were accumulated separately for men and women. In the following table, cells B3:C7 contain the actual percentage results by answer, split between men and women. Cells B9:C13 contain the calculated population expected values for a normal population, based on the sample. Each population expected value is computed by multiplying the sum of the row by the sum of the column and dividing by the grand total. |
A | B | C | |
|---|---|---|---|
1 | Answer | Men | Women |
2 | Actual | % | % |
3 | 5 | 15 | 9 |
4 | 4 | 56 | 35 |
5 | 3 | 11 | 23 |
6 | 2 | 10 | 23 |
7 | 1 | 8 | 10 |
8 | Expected | % | % |
9 | 5 | 12.00 | 4.50 |
10 | 4 | 10.20 | 3.83 |
11 | 3 | 4.92 | 1.85 |
12 | 2 | 4.82 | 1.81 |
13 | 1 | 4.21 | 1.58 |
=CHITEST(B3:C7, B9:C13) returns approximately 1.3382569E-218. Using this value and the degrees of freedom (4 in this example because there are 5 rows of actual values), you could use a chi-square table to determine if there is a statistically significant difference between the sample result and the expected population result. |