# Fisher Exact test - Can't replicate SPSS results in Excel

I've been doing some Fisher Exact tests in SPSS and am trying to do in Excel instead. So i can do automatically for rows of data. But I can't get the Excel results to match the results from SPSS.

The way to do test in SPSS is quite straightforward. This describes it. https://www.youtube.com/watch?v=6MbTlee-He8

I'd like to know why i don't have the 'Exact' option that other tutorials mention: https://www.youtube.com/watch?v=KomStQDeraY

I'm following this method for the Excel: https://www.real-statistics.com/chi-square-and-f-distributions/fishers-exact-test/

Focusing on the one-tail/side results, in excel I think i need to subtract the p-value from 1 sometimes, as i get values of 0.95 and above. I believe that these are significant results even though p is not < 0.05.

Then comparing the p-values, for 29 tests, i get up to 100% difference. Whether they are significant or not at the 0.05 level here is only 1 test that is different (0.07 in SPSS, 0.01 in Excel).

Does anyone know why this is happening and how i can do an appropriate method in both Excel and SPSS?

Thanks Rob

• Can you give specific data you want to analyze, show SPSS results, and show Excel results? Commented Aug 3, 2021 at 18:40
• Without seeing what you're doing it's hard to guess where your problem is. Commented Aug 3, 2021 at 23:01
• I'm unsure how to make more specific since it seems impossible to upload an excel file. But I've added a picture related to the Answer that BruceET kindly provided Commented Aug 4, 2021 at 13:22
• What is the $2\times 2$ table for your data? Before you did the experiment, did you suppose that a particular one of the two groups would have a higher proportion of Successes? // Some implementations of Fisher's Exact Test show results only for two-tailed tests. (I don't know about Excel. No idea what $0.9959$ refers to. When beginning to use a procedure in unfamiliar software it is a good idea to start by tying to duplicate results of a couple of worked textbook examples.) Commented Aug 4, 2021 at 18:01
• My data is company divisions and the category is male/female. I'm trying to calculate which directorates have significantly higher or lower proportion of females. So my 2 x 2 grids are Division/All Other Staff x Female/Male. Some divisions will have lower proportion of females (Analysis, Finance), and some will have higher (HR). Some will be very similar to the overall split (52% female). Commented Aug 5, 2021 at 14:02

Debugging strange results from various kinds of software is off-topic here. However, it may help you to figure out what is wrong, if I show output for Fisher's Exact Test as implemented in R. In the process you can try to learn the basics of Fisher's Exact Test. [You may also want to google the Wikipedia page on Fisher's Exact Test. And @whuber's Answer on this page listed in the margin of the current page.]

Example: Suppose Group A has 100 subjects of whom 72 answered Yes to a question and the rest answered No. Also, Group B has 110 subjects of whom 61 answered Yes, the rest No.

Then you have a $$2 \times 2$$ table TBL of results as follows:

TBL = rbind(c(72, 28), c(61, 49));  TBL
[,1] [,2]
[1,]   72   28
[2,]   61   49

fisher.test(TBL)

Fisher's Exact Test for Count Data

data:  TBL
p-value = 0.009389
alternative hypothesis:
true odds ratio is greater than 1
95 percent confidence interval:
1.223936      Inf
sample estimates:
odds ratio
2.058383


Group B has a significantly lower proportion of Yes's than does Group A. With P-value $$0.0094 < 0.01 = 1\%,$$ you have significance at the 1% level.

This test uses row marginal totals 100 (Group A), 110 (Group B) and the column marginal totals 133 (Yes) and 77 (No). The essential question is: "If I have 110 B's and 100 A's, what is the probability of seeing 61 or fewer B's among 133 Yes's?"

This is a hypergeometric probability, equivalent to the following: I have 110 green marbles (B's) in an urn and 110 red ones (A's). I withdraw 133 marbles (Yes's) without replacement, what is the probability I get 61 or fewer green ones.

The probability of getting exactly 61 Green marbles is $$P(X=61)=\frac{{110\choose 61}{100\choose 72}}{{210\choose 133}} =0,0052.$$

The probability of gettine 61 or fewer is the P-value $$P(X \le 61)=\sum_{i=0}^{61} \frac{{110\choose i}{100\choose 133-i}}{{210\choose 133}} = 0.0094.$$

dhyper(61, 110, 100, 133)  # hypergeom PDF
[1] 0.005246848

phyper(61, 110, 100, 133)  # hypergeom CDF
[1] 0.009388924


Traditionally, Fisher's Exact Test was used mainly for small counts (because of the tedious computations required for large counts), and an (approximate) chi-squared test was used for larger counts. In case one of your programs is giving you results from a chi-squared test. In R the one-sided chi-square test is implemented as prop.test. The approximate P-value $$0.0096,$$ is not much different from the P-value for Fisher's Exact Test.

prop.test(c(72,61), c(100,110), alt="g")

2-sample test for equality of proportions
with continuity correction

data:  c(72, 61) out of c(100, 110)
X-squared = 5.4829, df = 1, p-value = 0.009602
alternative hypothesis: greater
95 percent confidence interval:
0.04853051 1.00000000
sample estimates:
prop 1    prop 2
0.7200000 0.5545455


You might want to try data from my $$2 \times 2$$ table in SPSS and Excel to see whether results differ from the results obtained using R above. You might also be able to find worked examples for Fisher's test in various elementary and intermediate applied statistics textbooks.

• Thank you. I think what is happening is that when i do the test in Excel using the HYPGEOM distribution it gives you different results when you test whether the proportion of (up to) A successes is statistically significant, or whether the proportion of (up to A) fails is statistically significant. E.g. 0.996 vs 0.09. And the two values don't add up to 1. I thought it was the same test - just opposite so they should add up to 1. But actually both tests include the probability of exactly the observed number of fails/successes so they don't sum to 1. Commented Aug 4, 2021 at 14:10
• So i suppose i need to decide which direction i need to use the test. Maybe i should do a two tail test. Commented Aug 4, 2021 at 14:14
• Hi I'm now trying to do the Fisher Exact tests in R and want to integrate them into PowerBI. I'm struggling to match my excel tests with those done by R. Additionally, somehow I can't replicate your R results in R. TBL = rbind(c(72, 28), c(61, 49)); TBL [,1] [,2] [1,] 72 28 [2,] 61 49 fisher.test(TBL) Fisher's Exact Test for Count Data data: TBL p-value = 0.01499 alternative hypothesis: true odds ratio is not equal to 1 95 percent confidence interval: 1.11818 3.84029 sample estimates: odds ratio 2.058383 Do you have any ideas what could be happening? Commented Oct 25, 2023 at 14:28