I am looking for an Excel formula that solves the problem: for a given binomial distribution B(n, p) find all values for which B(n, p) < p₀ where p₀ ≤ p. The problem naturally reduces to finding only two threshold values for which B(n, p) values are the highest but no greater than p₀.

binomial.jpg

Here is an example of solving the problem manually. Suppose we have B(790, 0.117), i.e. the binomial distribution with 790 trials and 11.7% success probability, and p₀ = 0.02. So the goal is to find every number from 0 through 790 for which the distribution is no greater than 0.02.

Cycling through X values from 0 through 790 with the formula =BINOM.DIST(X, 790, 0.117, FALSE), we find the threshold values 80 (on the left of the peak) and 104 (on the right). That is, for every X lesser than 80 or greater than 104 the distribution will also be no greater than 0.02.

I failed to construct an Excel formula that can provide the same result instead of manual cycling through. If you can suggest such a formula, please test it with my example before posting answer to this question.