Question

How to calculate false discovery rate in excel?

Answer 1

Put the individual p-values in ascending order.Assign ranks to the p-values. For example, the smallest has a rank of 1, the second smallest has a rank of 2.Calculate each individual p-value’s Benjamini-Hochberg critical value, using the formula (i/m)Q, where:i = the individual p-value’s rank,m = total number of tests,Q = the false discovery rate (a percentage, chosen by you).Compare your original p-values to the critical B-H from Step 3; find the largest p value that is smaller than the critical value.

As an example, the following list of data shows a partial list of results from 25 tests with their p-values in column 2. The list of p-values was ordered (Step 1) and then ranked (Step 2) in column 3. Column 4 shows the calculation for the critical value with a false discovery rate of 25% (Step 3).



The bolded p-value (for Children) is the highest p-value that is also smaller than the critical value: .042 < .050. All values above it (i.e. those with lower p-values) are highlighted and considered significant, even if those p-values are lower than the critical values. For example, Obesity and Other Health are individually, not significant when you compare the result to the final column (e.g. .039 > .03). However, with the B-H correction, they are considered significant; in other words, you would reject the null hypothesis for those values.