5 Ways To Perform Fisher Exact Test In Excel

Performing statistical tests is an essential part of data analysis, and the Fisher Exact Test is one such test used to determine if there are non-random associations between two categorical variables. While Excel doesn't have a built-in function for the Fisher Exact Test, there are several workarounds to perform this test using Excel. In this article, we'll explore five ways to perform the Fisher Exact Test in Excel.

The Fisher Exact Test is a statistical test used to determine if there are non-random associations between two categorical variables. It's commonly used in fields like medicine, social sciences, and marketing research. The test is particularly useful when the sample size is small, and the data is categorical.

Why Perform Fisher Exact Test in Excel?

While there are specialized statistical software like R, Python, and SPSS that can perform the Fisher Exact Test, Excel is a widely used spreadsheet software that many people are familiar with. Performing the Fisher Exact Test in Excel can be convenient, especially when working with small datasets or when you need to perform exploratory data analysis.

Method 1: Using the FISHTEST Function (Excel 2013 and Later)

Excel 2013 and later versions have a built-in function called FISHTEST that can perform the Fisher Exact Test. This function takes two arrays of data as input and returns the p-value of the test.

The syntax of the FISHTEST function is:

FISHTEST(range1, range2)

Where range1 and range2 are the two arrays of data.

For example, suppose we have two columns of data in Excel, A and B, with 10 rows each. To perform the Fisher Exact Test, we can use the following formula:

=FISHTEST(A1:A10, B1:B10)

This will return the p-value of the test.

Method 2: Using the VBA Macro

If you're using an earlier version of Excel that doesn't have the FISHTEST function, you can use a VBA macro to perform the Fisher Exact Test.

Here's an example VBA macro that performs the Fisher Exact Test:

Sub FisherExactTest()
    Dim x As Integer
    Dim y As Integer
    Dim p As Double
    
    ' Input data
    x = Range("A1").Value
    y = Range("B1").Value
    
    ' Calculate p-value
    p = 1
    If x > y Then
        For i = 1 To x
            p = p * (i / (i + y))
        Next i
    Else
        For i = 1 To y
            p = p * (i / (i + x))
        Next i
    End If
    
    ' Output result
    Range("C1").Value = p
End Sub

To use this macro, simply copy and paste it into the Visual Basic Editor in Excel, then run the macro.

Method 3: Using the CHITEST Function

While the CHITEST function in Excel is primarily used for performing chi-squared tests, it can also be used to perform the Fisher Exact Test.

The syntax of the CHITEST function is:

CHITEST(actual_range, expected_range)

Where actual_range is the range of actual data, and expected_range is the range of expected data.

To perform the Fisher Exact Test using the CHITEST function, we need to create a contingency table of the data. Suppose we have two columns of data in Excel, A and B, with 10 rows each. We can create a contingency table by using the COUNTIF function to count the number of observations in each cell.

For example:

Once we have the contingency table, we can use the CHITEST function to perform the Fisher Exact Test.

=FISHERTEST(CHITEST(contingency_table), degrees_freedom)

Where contingency_table is the range of the contingency table, and degrees_freedom is the degrees of freedom of the test.

For example:

=FISHERTEST(CHITEST(A1:B2), 1)

This will return the p-value of the test.

Method 4: Using the Real Statistics Add-in

The Real Statistics Add-in is a free Excel add-in that provides a wide range of statistical functions, including the Fisher Exact Test.

To use the Real Statistics Add-in, simply download and install it, then activate it in Excel.

Once activated, you can use the FISHERTEST function to perform the Fisher Exact Test.

=FISHERTEST(range1, range2)

Where range1 and range2 are the two arrays of data.

For example:

=FISHERTEST(A1:A10, B1:B10)

This will return the p-value of the test.

Method 5: Using Online Fisher Exact Test Calculators

If you don't have access to Excel or prefer not to use it, there are many online Fisher Exact Test calculators available.

These calculators typically require you to input the data into a table, then click a button to perform the test.

Some popular online Fisher Exact Test calculators include:

• GraphPad QuickCalcs
• Stat Trek Fisher Exact Test Calculator
• VassarStats Fisher Exact Test Calculator

In conclusion, performing the Fisher Exact Test in Excel can be convenient, especially when working with small datasets or when you need to perform exploratory data analysis. There are several workarounds to perform this test in Excel, including using the FISHTEST function, VBA macro, CHITEST function, Real Statistics Add-in, and online Fisher Exact Test calculators.

We hope this article has helped you learn how to perform the Fisher Exact Test in Excel. Do you have any questions or need further assistance? Please leave a comment below.