Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating p-value from Fisher's Exact Test
Ian Smith Wrote: Me on Microsoft wrote in message ... 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 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 -- Ian Smith ------------------------------------------------------------------------ Ian Smith's Profile: http://www.excelforum.com/member.php...o&userid=27612 View this thread: http://www.excelforum.com/showthread...hreadid=268855 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | Excel Discussion (Misc queries) | |||
LookUp - Exact only | Excel Worksheet Functions | |||
LookUp - Exact only | Excel Worksheet Functions | |||
Identifying exact values in alternate rows | Excel Worksheet Functions |