fisher exact excel

Championisme authors

4 min read

·

14-12-2024

56

0

Share

Mastering Fisher's Exact Test in Excel: A Comprehensive Guide

Fisher's exact test is a powerful statistical method used to analyze the association between two categorical variables in a contingency table, particularly when sample sizes are small. Unlike the chi-squared test, Fisher's exact test doesn't rely on asymptotic approximations, making it more accurate and reliable for small datasets. This article explores Fisher's exact test, its application, limitations, and how to perform it in Excel, both manually and using add-ins.

Understanding Fisher's Exact Test

Fisher's exact test assesses the probability of observing a contingency table as extreme as, or more extreme than, the one obtained from the sample data, given that there's no association between the two variables. It's particularly useful when the expected cell counts in a chi-squared test are less than 5, a situation where the chi-squared approximation becomes unreliable.

Let's consider a classic example: We want to investigate the relationship between gender (male/female) and preference for a particular brand of coffee (Brand A/Brand B). We collect data and present it in a 2x2 contingency table:

Where:

• 'a' represents the number of males who prefer Brand A.
• 'b' represents the number of males who prefer Brand B.
• 'c' represents the number of females who prefer Brand A.
• 'd' represents the number of females who prefer Brand B.
• N = a + b + c + d is the total sample size.

Fisher's exact test calculates the probability of observing this specific table, or a more extreme table (with even more pronounced differences in proportions), assuming the null hypothesis (no association between gender and coffee preference) is true. A small p-value (typically less than 0.05) leads to the rejection of the null hypothesis, suggesting a statistically significant association.

Calculating Fisher's Exact Test Manually in Excel (for 2x2 tables)

While Excel doesn't have a built-in function for Fisher's exact test, we can calculate it manually using the HYPGEOM.DIST function. This function calculates the hypergeometric probability, which is the foundation of Fisher's exact test. The one-tailed p-value is calculated as follows:

• Step 1: Identify the smallest cell value: Let's say the smallest cell value is 'a'.
• Step 2: Calculate the one-tailed p-value: This is the sum of the probabilities of observing tables as extreme as or more extreme than the observed table in one direction. This involves calculating the hypergeometric probability for the observed table and adding the probabilities of more extreme tables, adjusting the smallest cell (a) sequentially.

For example, if a=2, b=3, c=4, d=1:

• Observed table probability: =HYPGEOM.DIST(2, 5, 6, 5, FALSE)

• More extreme tables: We calculate probabilities for tables with a=1, a=0, then add them to the observed probability. This needs careful attention to the constraints of the marginal totals (a+b, a+c, b+d, c+d).

• Two-tailed p-value: The two-tailed p-value is typically double the one-tailed p-value unless the one-tailed p-value exceeds 0.5.

Important Note: This manual calculation can be tedious and prone to errors, especially for larger tables. It's crucial to meticulously check each calculation.

Using Excel Add-ins for Fisher's Exact Test

Several Excel add-ins offer simplified computation of Fisher's exact test. These add-ins often provide a more user-friendly interface and automatically handle the complexities of the calculation, including the two-tailed p-value. Examples include Real Statistics Resource Pack and XLSTAT. Installing and using these add-ins typically involves a straightforward procedure that varies slightly depending on the specific add-in. Consult the individual add-in's documentation for detailed instructions.

Interpreting the Results

The output of Fisher's exact test is the p-value. If the p-value is less than the pre-determined significance level (usually 0.05), we reject the null hypothesis and conclude that there is a statistically significant association between the two categorical variables. If the p-value is greater than 0.05, we fail to reject the null hypothesis, suggesting insufficient evidence to claim an association.

Limitations of Fisher's Exact Test

• Computational intensity: For larger contingency tables, manual calculation is impractical.
• Conservative nature: Fisher's exact test is considered conservative, meaning it may have lower power compared to the chi-squared test in some situations, particularly with larger sample sizes.
• Only for categorical data: Fisher's exact test is specifically designed for categorical variables; it is inappropriate for continuous data.

Examples Beyond 2x2 Tables

While the 2x2 contingency table is the most common application, Fisher's exact test can be generalized to larger tables (rxc), although the calculations become significantly more complex. Add-ins are necessary for efficient computation in these scenarios.

Practical Applications

Fisher's exact test finds application in various fields:

• Medical research: Evaluating the relationship between treatment and outcome in clinical trials with small sample sizes.
• Market research: Analyzing the association between demographic variables (e.g., age, gender) and consumer preferences.
• Genetics: Assessing the association between genetic markers and disease status.
• Social sciences: Studying the relationship between social factors and behaviors.

Conclusion

Fisher's exact test is a valuable tool for analyzing the association between categorical variables, particularly when sample sizes are small. While manual calculation is feasible for 2x2 tables, utilizing Excel add-ins significantly simplifies the process, especially for larger contingency tables. Remember to correctly interpret the p-value and consider the test's limitations when drawing conclusions. Always consider the practical implications of your findings in the context of the research question and the limitations of the data. Further exploration of statistical software packages like R or SPSS can provide more advanced features and analyses beyond the scope of Excel.