Microsoft Office Tutorials and References
In Depth Information
Comparing Results to an Estimate
matrix of degrees of freedom and confidence levels. Seeing where the
calculated value is positioned in the table for the appropriate degrees of freedom
(one less than the number of data points) will show you the probability that
the difference between the expected and observed values is significant. That
is, is the difference within a reasonable error of estimation or is it real (for
example, caused by an unbalanced coin)?
The table of degrees of freedom and confidence levels is often found in the
appendix of a statistics book or can be found on the Internet.
The CHISQ.TEST function returns the probability value (p) derived from the
expected and observed ranges. There are two arguments to the function: the
range of observed (or actual) values and the range of expected values. These
ranges must, of course, contain the same number of values, and they must be
matched (first item in the expected list is associated with the first item in the
observed list, and so on). Internally, the function takes the degrees of freedom
into account, calculates the Chi Square statistic value, and computes the
Use the CHISQ.TEST function this way:
2. Position the cursor in the cell where you want the result to appear.
3. Enter =CHISQ.TEST( to start the function.
4. Drag the cursor over the range of observed (actual) values, or enter
the address of the range.
5. Enter a comma ( ,).
address of the range.
7. Enter a ).
Figure 10-3 shows a data set of expected and actual values. The Chi Square
test statistic is calculated as before, delivering a value of 1.594017, seen in
cell F12. The CHISQ.TEST function, in cell D14, returns a value of 0.953006566,
the associated probability. Remember that CHISQ.TEST doesn’t return the
Chi Square statistic but rather the associated probability.
Now tie in a relationship between the manually calculated Chi Square and the
value returned with CHISQ.TEST. If you looked up your manually calculated
Chi Square value (1.59) in a Chi Square table for degrees of freedom of 6 (one
less than the number of observations), you would find it associated with a
probability value of 0.95. Of course, the CHISQ.TEST function does this for you,