Fisher’s Exact in Excel

Here we introduce the Fisher’s Exact (FE) test, a simple but very useful statistical measure in situations involving binary (e.g., Yes/No, Win/Lose, Hired/Not Hired) outcomes affecting two distinct classes.  Unlike most other common statistical measures such as the chi-squared test or regression, FE is particularly adept at dealing with small (n < 20) sample sizes.  The general FE layout is depicted below:

Note that we calculate totals for each row and column, as well as a grand total in the lower right cell.  The FE null hypothesis states that the outcomes {A,B,C,D}, individually and collectively, are all due to random chance.

Stated somewhat differently, FE is based on the idea that the probability of obtaining {A,B,C,D} in that exact quantity and that exact order is determined by the hypergeometric distribution:

$P\left(X=k\right)=\frac{\binom{m}{k}\binom{N-m}{n-k}}{\binom{N}{n}}$

where N is the population size: A+B+C+D,

m/N is the non-biased probability of a favorable outcome,

n is the number of chance occurrences (e.g., coin flips, football games, hiring decisions, etc.), and

k is the observed number of favorable outcomes.

In the context of labor and employment law, the two classes are invariably some protected category (based on race, age, gender, etc.) and another class that includes everyone else.

For example, assume a firm is undertaking a necessary headcount reduction within a large department, and further assume that the displacements fell in accordance with the distribution set forth below:

Assuming these numerical data are positioned in cells B2:D4, the FE argument would be this:

=COMBIN(B2+C2,B2)*COMBIN(B3+C3,B3)/COMBIN(D4,B2+B3)

Note that the returned p-value is approximately 0.38.

By convention, the FE threshold for statistical significance is p </= 0.05.  Consequently, we cannot reject the null hypothesis that this hypothetical displacement distribution was the result of random chance.

Obviously, unlike regression, FE cannot account for the effect of category-neutral variables like seniority, or subjective variables like performance.  And while neither DOL nor OFCCP currently endorse the use of FE as a statistical indicator, it nevertheless provides a very good first-level screen for the presence of a pattern that may warrant further investigation.

This entry was posted in Built-In Functions, Excel. Bookmark the permalink.