You're going to need a function to calculate the tail probabilities of
the hypergeometric distribution. VBA code for these calculations can
be found in
http://members.aol.com/iandjmsmith
Then the p-value for the two sided test for the 2x2 table
a b
c d
is given by Fishers_exact_test(a,b,c,d)
Public Function Fishers_exact_test(a As Double, b As Double, _
c As Double, d As Double) As Double
Dim det As Double
Dim temp As Double
Dim sample_size As Double
Dim pop As Double
det = a * d - b * c
If det 0 Then
temp = a
a = b
b = temp
temp = c
c = d
d = temp
det = -det
End If
sample_size = a + b
temp = a + c
pop = sample_size + c + d
det = (2 * det + 1) / pop
If det < -1# Then
Fishers_exact_test = cdf_hypergeometric(a, sample_size, temp, pop) _
+ comp_cdf_hypergeometric(a - det, sample_size, temp, pop)
Else
Fishers_exact_test = 1#
End If
End Function