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

probability.

Use the CHISQ.TEST function this way:

1.Entertworangesofvaluesasexpectedandobservedresults.

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 (
,).

6.Dragthecursorovertherangeofexpectedvalues,orenterthe

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,