[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

CHITEST

Syntax:

CHITEST ( actual-range , expected-range )

Description: Computes the test for independence. CHITEST returns the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom.

Mathematical Formula:

The χ2 test first calculates a χ2 statistic using the formula:

where:

Aij = actual frequency in the i-th row, j-th column
Eij = expected frequency in the i-th row, j-th column
r = number or rows
c = number of columns

CHITEST uses the χ2 distribution with an appropriate number of degrees of freedom, df. If r > 1 and c > 1, then df = (r - 1)(c - 1). If r = 1 and c > 1, then df = c - 1 or if r > 1 and c = 1, then df = r - 1.

Arguments:

Name

Type

Description

actual-range

reference

The range of data that contains observations to test against expected values.

expected-range

reference

The range of data that contains the ratio of the product of row totals and column totals to the grand total.

 

Return Type and Value: number – The value from the chi-squared distribution for the statistic and the appropriate degrees of freedom.

However, if:

The number of rows and columns is exactly one, the return value is unspecified.

actual-range and expected-range have a different number of data points, the return value is unspecified.

[Example: Given the following data:

 

A

B

C

1

Men (Actual)

Women (Actual)

Description

2

58

35

Agree

3

11

25

Neutral

4

10

23

Disagree

5

Men (Expected)

Women (Expected)

Description

6

45.35

47.65

Agree

7

17.56

18.44

Neutral

8

16.09

16.91

Disagree


CHITEST(A2:B4,A6:B8) results in 0.000308

end example]