Calculate FDR in Excel

As you run tests and analysis tasks, it’s vitally important to capture the number of false discoveries. This helps validate your research processes to drive improvements in accuracy. The measurement is called the false discovery rate (FDR). Fortunately, this is very easy to calculate in Excel, thanks to a very simple formula. Let’s learn how to calculate FDR in Microsoft Excel.

How to Calculate FDR in Excel

To calculate the false discovery rate (FDR) in Excel, you’ll need to have completed your experiment or analysis. The FDR formula requires only two inputs based on positive tests:

• The number of false positives, which are cases where a positive result was inaccurately returned.
• The total number of positives, incorporating both true positives and false positives.

Imagine a hypothetical set of data gathered from an experiment. 150 tests were administered to 150 volunteers. Of the 80, there were 11 false positives, and 52 true positives. Now, you want to find the FDR to determine, by percentage basis, how many tests are false discoveries.

Your initial inputs here are 11 and 52. The formula for FDR is quite simple: false positives / total positives. But keep in mind, your total positive value isn’t 52. It’s actually 63, as it includes the 11 false positives.

Input 11 and 63 into any pair of Excel cells, like B1 and B2. Then, create a simple formula in a third cell:

=B1/B2

Hit Enter on your keyboard, and Excel returns a solution: 0.174603175. To convert it to a percentage, go to Excel’s Home tab and click on Percent Style. The FDR value will instantly convert to 17%, representing the false discovery rate present in your experiment.

As you can see, it’s easy to calculate FDR in Excel, all thanks to a quick and simple formula that you can build in seconds.