FISHER_EXACT

Overview

The FISHER_EXACT function performs Fisher’s exact test on a contingency table. For 2x2 tables, this test examines whether the odds ratio differs significantly from 1, while for larger tables it tests independence. The test is “exact” because it calculates exact p-values rather than relying on asymptotic approximations, making it suitable for small sample sizes.

Excel does not provide Fisher’s exact test. While the hypergeometric distribution functions (HYPGEOM.DIST) could theoretically be used for manual calculation, implementing Fisher’s exact test requires computing probabilities for all possible tables with the same marginal totals and determining which are “as extreme” as the observed table. This complex combinatorial calculation is not practical with standard Excel functions and would require significant custom programming.

For more details, see the Scipy documentation .

This example function is provided as-is without any representation of accuracy.

Usage

To use the function in Excel:


=FISHER_EXACT(table, [alternative])

table (2D list, required): Contingency table with non-negative integer entries. Must be at least 2x2.
alternative (string, optional, default=‘two-sided’): For 2x2 tables, defines the alternative hypothesis. Options are ‘two-sided’, ‘less’, ‘greater’. Ignored for larger tables.

The function returns a 2D list with one row and two columns: the test statistic (odds ratio for 2x2 tables, probability mass for larger tables) and the p-value.

Examples

Example 1: 2x2 Table, Two-Sided

Inputs:

table		alternative
8	2	two-sided
1	5

Excel formula:


=FISHER_EXACT({8,2;1,5}, "two-sided")

Expected output:

Statistic	P-value
20.0	0.0349650

Example 2: 2x2 Table, Greater

Inputs:

table		alternative
8	2	greater
1	5

Excel formula:


=FISHER_EXACT({8,2;1,5}, "greater")

Expected output:

Statistic	P-value
20.0	0.0244755

Example 3: 2x2 Table, Less

Inputs:

table		alternative
8	2	less
1	5

Excel formula:


=FISHER_EXACT({8,2;1,5}, "less")

Expected output:

Statistic	P-value
20.0	0.995338

Example 4: 2x3 Table

Inputs:

table
8	2	3
1	5	4

Excel formula:


=FISHER_EXACT({8,2,3;1,5,4})

Expected output:

Statistic	P-value
0.005782	0.0603

Python Code


from scipy.stats import fisher_exact as scipy_fisher_exact
import numpy as np
 
def fisher_exact(table, alternative='two-sided'):
    """
    Perform Fisher's exact test on a contingency table.
 
    Args:
        table: 2D list (2x2 or larger) contingency table with non-negative integer entries.
        alternative: For 2x2 tables, defines the alternative hypothesis. Options are 'two-sided', 'less', 'greater'.
 
    Returns:
        A 2D list containing the test statistic (odds ratio for 2x2 tables, probability mass for larger tables) and p-value, each as a separate element in the first row.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate table
    if not isinstance(table, list) or len(table) < 2 or not all(isinstance(row, list) and len(row) >= 2 for row in table):
        return [["Invalid input: table must be a 2D list with at least 2 rows and 2 columns.", None]]
    try:
        arr = np.array(table)
        if arr.ndim != 2 or arr.shape[0] < 2 or arr.shape[1] < 2:
            return [["Invalid input: table must be a 2D list with at least 2 rows and 2 columns.", None]]
        if np.any(arr < 0) or not np.issubdtype(arr.dtype, np.integer):
            return [["Invalid input: table must contain non-negative integers only.", None]]
    except Exception:
        return [["Invalid input: table must be convertible to a 2D array of non-negative integers.", None]]
    # Validate alternative
    if arr.shape == (2,2):
        if alternative not in ['two-sided', 'less', 'greater']:
            return [["Invalid input: alternative must be 'two-sided', 'less', or 'greater' for 2x2 tables.", None]]
        try:
            res = scipy_fisher_exact(arr, alternative=alternative)
            stat, pval = res.statistic, res.pvalue
        except Exception as e:
            return [[f"scipy.stats.fisher_exact error: {e}", None]]
    else:
        try:
            res = scipy_fisher_exact(arr)
            stat, pval = res.statistic, res.pvalue
        except Exception as e:
            return [[f"scipy.stats.fisher_exact error: {e}", None]]
    # Check for invalid outputs
    if not (isinstance(stat, (int, float)) and isinstance(pval, (int, float))):
        return [["Invalid output from scipy.stats.fisher_exact.", None]]
    if np.isnan(stat) or np.isinf(stat) or np.isnan(pval) or np.isinf(pval):
        return [["Invalid output: statistic or p-value is NaN or infinite.", None]]
    return [[float(stat), float(pval)]]

Example Workbook

Link to Workbook