Data Analysis with Microsoft Excel: Updated for Office 2007

296 Fundamentals of Statistics

Table 7-2 Counts for Calculus Requirement by Department

Observed Expected Difference

74 73.23 0.77

25 20.57 4.43

77 97.92 2 20.92

98 82.28 15.72

Sum 0.00

Because this sum is 0, the last difference can be calculated on the basis of

the previous three, and there are only three cells that are free to vary in value.

Applied to the whole table, this means that if we know 3 of the 8 differences,

then we can calculate the values of the remaining 5 differences. Hence the

number of degrees of freedom is 3.

Working with the x

2

Distribution in Excel

Now that we know the value of the test statistic and the degrees of freedom,

we are ready to test the null hypothesis. Excel includes several functions to

help you work with the x^2 distribution. Table 7-3 shows some of these.

Table 7-3 Excel Functions for x^2 Distribution

Function Description
CHIDIST(x, df) Returns the p value for the x^2 distribution for a given
value of x and degrees of freedom df.
CHIINV(p, df) Returns the x^2 value from the x^2 distribution with
degrees of freedom df and p value p.
CHITEST(observed, expected) Calculates the Pearson chi square, where observed is a
range containing the observed counts and expected is
a range containing the expected counts.
PEARSONCHISQ(observed) Calculates the Pearson chi-square, where observed
is the table containing the observed counts. StatPlus
required.
PEARSONP(observed) Calculates the p value of the Pearson chi-square, where
observed is the table containing the observed counts.
StatPlus required.

The output you generated earlier displays (among other things) the value

for the Pearson chi-square statistic. The x^2 value is 47.592 with a p value of

less than 0.001. Because this probability is less than 0.05, you reject the null

hypothesis that the calculus requirement does not differ on the basis of the

department (not surprisingly).