XLSTAT: Calcuating Chi Square and Fisher's Exact Test in Excel

 

Background

The Chi-squared and Fisher’s Exact Tests challenge the same  null hypothesis and may thus answer the same question. 

The Chi-square test is based on a statistic which reflects the distance between the real data and theoretical data if the null hypothesis were true. Chi-Square is an approximation test, best suited for large sample sizes. The higher the chi-square statistic, the lower the p-value.

Fisher’s exact test is based on the probability of having the observed data and the probabilities of getting all of the more extreme possible datasets under the null hypothesis. Fisher's Exact Test provides an exact probability calculation, making it more appropriate for small sample sizes, particularly when cells in the table have very low expected counts. 

Dataset for Example

The contingency table containing counts of a bananas collected on a market stand. The table crosses two qualitative variables: banana variety in columns (3 categories: 1, 2 and 3) and presence of maggots in the banana in rows (two categories: presence, absence).

Each cell contains the number of bananas having the combination of the corresponding row and column categories. 

The aim of this example is to use the Chi-square and Fisher's Exact Tests to test the association between two qualitative variables measured on a banana sample: variety and presence/absence of maggots.

Possible results are:

H0 (null hypothesis) : The two qualitative variables are independent. 

• In our example, this would mean that the presence/absence of maggots is independent of the banana variety.

Ha (alternative hypothesis) : The two qualitative variables are dependent on each other. 

• In our example, this would mean that the presence/absence of maggots depends on banana variety.

The difference between the two tests lies in the way the p-value is calculated.

Setting Up

• Open XLSTAT.
• Click on the Correlation – Association tests menu and select the Tests on contingency tables feature:

In the General tab:

• Select the Contingency table data format (if not enabled)
• Select your data in the Contingency table field.

In the case where your data is vertical (individuals in rows and variables in columns)

• enable the Qualitative variables data format option.

• In the Options tab, enable the Chi-square test and Fisher’s exact test options.

 

• In the Outputs tab, enable Theoretical frequencies and Proportions / Column.

• Click on the OK button to launch the computations.
• The results are displayed on a new Excel sheet.

NOTE: If you wish to transform your data from a vertical format to a contingency table format, see this Cross Tab/Contingency Tab Page

Interpreting the Results

First, XLSTAT displays the results associated to the Chi-square test.

The p-value (0.033) is lower than the significance level of 0.05. Thus, we reject the null hypothesis that maggot presence and banana variety are independent, with a 3.3% risk of being incorrect.

Next, Fisher’s exact test results are displayed.

Here also, the p-value (0.044) is lower than the significance level 0.05. Fisher’s exact test also leads to a rejection of the null hypothesis.

Notice that the p-value obtained under the Chi-square test is slightly lower than the one associated to Fisher’s exact test.

Fisher's exact test (Significance by cell) indicates for each cell, if the actual value is:

• equal (=)
• lower (<)
• higher (>) than the theoretical value

 P-values per cell of the contigency table are also provided in a separate table.

The theoretical frequencies table displays frequencies that would have been obtained if the null hypothesis were true.

A simple calculation shows that the proportion of bananas with maggots is the same across varieties (11.8%). Since one of the frequencies is lower than 5, we will only report the results of the Fisher’s Exact Test.

The  Proportions / Column table shows the proportions of Maggots-Yes and Maggots-No bananas for each of the three varieties. According to the result of the tests, we may say that at least one of the (0.121, 0.104 and 0.300) proportions is significantly different from the others.

Related Article

Statistical Test Guide