How to use CHISQ.TEST Function in Excel

The CHISQ.TEST function returns the test for independence. CHISQ.TEST returns the value from the chi-squared (χ2) distribution for the statistic and the appropriate degrees of freedom. You can use χ2 tests to determine whether hypothesized results are verified by an experiment.

Syntax:= CHISQ.TEST(actual_range,expected_range)

The CHISQ.TEST function syntax has the following arguments:

  1. Actual_range (required argument) – This is the range of data containing observations that are to be tested against expected values. It is an array of observed frequencies.
  2. Expected_range (required argument) – This is the range of data that contains the ratio of the product of row totals and column totals to the grand total. It is an array of expected frequencies.

Example: Let’s look at some Excel CHISQ.TEST function examples and explore how to use the CHISQ.TEST function as a worksheet function in Microsoft Excel:

CHISQ.TEST Function - How to use CHISQ.TEST Function in Excel

The formula for calculating the chi-square test for the independence of the datasets above is:

Syntax:  =CHISQ.TEST(B3:C5,E3:F5)

Result: 0.0001180

CHISQ.TEST Function in Excel 1 - How to use CHISQ.TEST Function in Excel

Generally, a probability of 0.05 or less is considered to be significant. Therefore, the returned value above, 0.0000217, indicates a significant difference between the observed and the expected frequencies, which is unlikely to be due to sampling error.

Note:

  1. #DIV/0! error – Occurs when any of the values provided in expected_range is zero.
  2. #N/A error – Occurs when either:
    1. The data arrays provided are of different dimensions; or
    2. The data arrays provided contain only one value. That is, the length and width are equal to 1.
  3. #NUM! error – Occurs when any of the values in the expected range is negative.
  4. The CHISQ.TEST function was introduced in Excel 2010 and hence is unavailable in earlier versions. It is an updated version of the CHITEST function.
  5. The χ2 test first calculates a χ2 statistic using the formula:
    CHISQ.TEST Function in Excel - How to use CHISQ.TEST Function in Excel
READ:  How to use FISHER Function in Excel

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

Add a Comment

Your email address will not be published. Required fields are marked *