The chi square test allows us to determine whether two different sets of data are existing independently.

There are many statistical distributions programmed into Microsoft Excel; the chi square tests are examples of such distributions.

Figure 1. of Chitest in Excel

Generic Formula

CHISQ.TEST=(actual_range,expected_range)

The syntax for the chisq.test formula comprises of the following arguments:

-Actual_range or R1 (required) The data range which contains the observations we would like to test against our expected values.

-Expected_range or R2 (required) The data range containing the product ratio of the row and column totals against the grand total.

How to do Chi Square Test

We can extend the chisq.test function to support ranges which consist of multiple columns and rows.

The R1 and R2 ranges should have the same shape and size and must contain numeric values only.

Let’s assume we have a data range of values containing the results of a survey conducted among several adults having different horoscopic events, and another range of expected values which we are required to run a chi-square test for their degree of independence.

  1. We start by arranging our data sets in separate columns of our worksheet;

Figure 2. Of Data Sets for Chitest

  1. The chisq.test formula we will enter into cell E5 of the above worksheet example is as follows:

CHISQ.TEST(B2:B13, C2:C13) – this should test for the degree of freedom of the data sets in columns B and C of our worksheet

Figure 3. of Chitest in Excel

The chitest formula automatically returns our independence test value for our specified ranges of data.

Based on the result obtained from calculating chi square for our worksheet example, we can conclude that there is no significant difference in the dependence rate between our two data values.

Note

  • If actual  and expected ranges have a varying number of data points, chitest returns an error value.
  • The chi squared test can be of use in verifying  hypothesized results of an experiment.
  • For Microsoft Excel versions prior to 2010, the chi squared in excel function is not available. Instead we have to use an equivalent CHITEST function.