I am wondering how to perform a Fishers exact test on my data which contains two columns and three rows. One of the cells contains zero.

             2012    2018
Pass          116     166
Submax         11      45
Fail            9       0

I would like to compare the data between the years 2012 and 2018. Any help would be much appreciated! I only have access to excel but if anyone else can calculate it for me that would be brilliant.

  • 1
    $\begingroup$ AFAIK this isn't something Excel can do. The counts are far too large for this to be done manually by anyone, either. The only non-software aspect of your question seems to be the mention of a cell with a zero. Could you explain why (if at all) that might be a concern? $\endgroup$
    – whuber
    Dec 5 '19 at 22:13
  • 7
    $\begingroup$ It might be time to graduate to statistical software. The following R code can be run at: rdrr.io/snippets . Y2012 = c(116, 11, 9); Y2018 = c(166, 45, 0); Matrix = cbind(Y2012, Y2018); rownames(Matrix) = c("Pass", "Submax", "Fail"); Matrix; fisher.test(Matrix) $\endgroup$ Dec 5 '19 at 23:15
  • $\begingroup$ You could also conduct the analysis with free menu-based software like Jamovi. $\endgroup$ Dec 5 '19 at 23:21
  • $\begingroup$ I'll stand partially corrected on my comment on Jamovi. Jamovi is (currently) my favorite gui-menu-based free statistical software package. But for some reason, it will report Fisher's exact test only for 2 x 2 tables. This is unexpected since R reports the exact test for larger tables as well. $\endgroup$ Dec 6 '19 at 2:51
  • 1
    $\begingroup$ @kjetilbhalvorsen , interestingly, that online calculator won't calculate the exact test if N > 300, so it won't work in this case. I imagine this calculator, along with Jamovi, has these kinds of limits to avoid the software hanging or producing errors. I suppose the upshot is that, if one wants to get into reasonably complex statistical analysis, there's no substitute for using good software. $\endgroup$ Dec 6 '19 at 11:23

In General

Wolframalpha has a page about the manual computation of Fisher exact test: http://mathworld.wolfram.com/FishersExactTest.html

The probability of occurance for a particular combination of cell values $a_{ij}$ for given row sums $R_i$, column sums $C_j$, and total sum $N$:

$$P(a_{ij}) = \frac{\prod_{\forall i } R_{i}! \prod_{\forall j } C_{j}!}{N! \prod_{\forall i,j } a_{ij}!} $$

To find a p-value you would have to compute the cumulative probability: compute the values for tables $a_{ij}$ with more extreme difference and sum them together.

Manual computation example

For a 2x2 table this can be easily done. For a 2x3 table it can also be done (although not very simple).

All the possibly combinations with the same row totals can be summarized by two parameters $a$, $b$

             116+b       166-b    ¦ 282
              11+a-b      45-a+b  ¦  56 
               9-a         0+a    ¦   9
             136         211      ¦ 347

So all you need to do is compute $P(a_{ij})$ for all values of a and b and sum those which are equal or extremer.


Automized method

Those manual computations have been automized. In R for instance you can get the result by typing a single line:

fisher.test(matrix(c(116, 11, 9, 166, 45, 0), 3))


Fisher's Exact Test for Count Data

data:  matrix(c(116, 11, 9, 166, 45, 0), 3)
p-value = 2.476e-06
alternative hypothesis: two.sided

This function in R goes through all the options (like in the manual excel sheet but automatic). However when the tables get large then the computation will take a lot of time, also for a computer. The function in R can also estimate a p-value by generating random tables and see how often a worse deviation is observed.


Not the answer you're looking for? Browse other questions tagged or ask your own question.