Fisher’s Exact Test is used to determine whether or not there is a significant association between two categorical variables. It is typically used as an alternative to the Chi-Square Test of Independence when one or more of the cell counts in a 2×2 table is less than 5.
This tutorial explains how to perform Fisher’s Exact Test in Excel.
Example: Fisher’s Exact Test in Excel
Suppose we want to know whether or not gender is associated with political party preference at a particular college. To explore this, we randomly poll 25 students on campus. The number of students who are Democrats or Republicans, based on gender, is shown in the table below:
To determine if there is a statistically significant association between gender and political party preference, we can perform Fisher’s Exact Test.
Although Excel doesn’t have a built-in function to perform this test, we can use the hypergeometric function to perform the test, which uses the following syntax:
=HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)
- sample_s = the number of “successes” in the sample
- number_sample = the sample size
- population_s = the number of “successes” in the population
- number_pop = the population size
- cumulative = if TRUE, this returns the cumulative distribution function; if FALSE, this returns the probability mass function. For our purposes, we will always use TRUE.
To apply this function to our example, we will pick one of the four cells in the 2×2 table to use. Any cell will do, but we’ll use the top left cell with the value “4” for this example.
Next, we’ll fill in the following values for the function:
=HYPGEOM.DIST(value in individual cell, total column count, total row count, total sample size, TRUE)
This produces a one-tailed p-value of 0.0812.
In order to find the two-tailed p-value for the test, we will add the following two probabilities together:
- The probability of getting x “successes” in the cell we’re interested in. In our case, this is the probability of getting 4 successes (we already found this probability to be 0.0812).
- 1 – the probability of getting (total column count – x “successes”) in the cell we’re interested in. In this case, the total column count for Democrat is 12, so we’ll find 1 – (probability of 8 “successes”)
Here’s the formula we’ll use:
This produces a two-tailed p-value of 0.1152.
In either case, whether we conduct a one-tailed test or a two-tailed test, the p-value is not less than 0.05 so we cannot reject the null hypothesis. In other words, we don’t have sufficient evidence to say that there is a significant association between gender and political party preference.
How to Perform a Chi-Square Test of Independence in Excel
How to Perform a Chi-Square Goodness of Fit Test in Excel
How to Calculate Cramer’s V in Excel