1
$\begingroup$

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

Fisher Test in Excel

$\endgroup$
5
  • $\begingroup$ Can you give specific data you want to analyze, show SPSS results, and show Excel results? $\endgroup$
    – BruceET
    Commented Aug 3, 2021 at 18:40
  • $\begingroup$ Without seeing what you're doing it's hard to guess where your problem is. $\endgroup$
    – Glen_b
    Commented Aug 3, 2021 at 23:01
  • $\begingroup$ 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 $\endgroup$
    – Rob Green
    Commented Aug 4, 2021 at 13:22
  • $\begingroup$ 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.) $\endgroup$
    – BruceET
    Commented Aug 4, 2021 at 18:01
  • $\begingroup$ 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). $\endgroup$
    – Rob Green
    Commented Aug 5, 2021 at 14:02

1 Answer 1

5
$\begingroup$

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.

$\endgroup$
3
  • $\begingroup$ 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. $\endgroup$
    – Rob Green
    Commented Aug 4, 2021 at 14:10
  • $\begingroup$ So i suppose i need to decide which direction i need to use the test. Maybe i should do a two tail test. $\endgroup$
    – Rob Green
    Commented Aug 4, 2021 at 14:14
  • $\begingroup$ 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? $\endgroup$
    – Rob Green
    Commented Oct 25, 2023 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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