Calculating p-value from Fisher's Exact Test

[Me on Microsoft]

Say I have a 2x2 table like:

1 5
30 20

and I want to determine whether the rows are significantly different. I'd
like to use Fisher's Exact Test, and because I'm not a statistician I'd like
to cut to the chase and just calculate the p-value associated with the
two-sided test.

Can I do this in Excel? If so, how?

Thanks!

 

[Ian Smith]

Say I have a 2x2 table like:

1 5
30 20

and I want to determine whether the rows are significantly different. I'd
like to use Fisher's Exact Test, and because I'm not a statistician I'd like
to cut to the chase and just calculate the p-value associated with the
two-sided test.

Can I do this in Excel? If so, how?

Thanks!

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/Examples.xls.

You'll then need to add the VBA function

Public Function Fishers_exact_test(a As Double, b As Double, c As
Double, d As Double) As Double
Dim det As Double, temp As Double, sample_size As Double, 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
Fishers_exact_test = cdf_hypergeometric(a, sample_size, temp, pop) +
comp_cdf_hypergeometric(a - 2 * det / pop, sample_size, temp, pop)
End Function

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)

Ian Smith

 

[Ian Smith]

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/Examples.xls.

You'll then need to add the VBA function

Public Function Fishers_exact_test(a As Double, b As Double, c As
Double, d As Double) As Double
Dim det As Double, temp As Double, sample_size As Double, 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
Fishers_exact_test = cdf_hypergeometric(a, sample_size, temp, pop) +
comp_cdf_hypergeometric(a - 2 * det / pop, sample_size, temp, pop)
End Function

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)

Ian Smith

The VBA function should be

Public Function Fishers_exact_test(a As Double, b As Double, c As
Double, d As Double) As Double
Dim det As Double, temp As Double, sample_size As Double, 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


Thanks to Einar Andreas Rødland for spotting the error!

Ian Smith

 

[Myrna Larson]

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