Formula for binomial threshold values

**-=-** · 06-04-2020, 11:06 AM

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.

**MrShorty** · 06-04-2020, 11:36 AM

You haven't shared what you have tried, so I am not sure what process(es) you were trying to use. To my knowledge, there is not a convenient "inverse binomial PDF" function, though there is the BINOM.INV() function that returns the inverse of the binomial CDF.

Are you required to come up with a formula only solution? For a function like this without a convenient inverse, I usual start by using goal seek or Solver (I usually prefer Solver because it is more robust). Set up your forward calculation (=BINOM.DIST(X, 790, 0.117, FALSE) where X refers to a cell with your best first guess at the solution), then call Solver and tell it to set the formula cell to a value of 0.02 by changing the X cell. Repeat with a different starting guess on the other side of the distribution to find the other solution.

Will something like that work for you, or are you prohibited from using Solver/Goal Seek for this problem?

**-=-** · 06-04-2020, 12:25 PM

Using Solver for every problem of this type is even more cumbersome than my brute-force method. Suppose I have not the single problem, but dozens of problems of this type. If I will solve each one manually, I never get my work done. So I look for a formula where I can input n, p and p₀ and instantly get two numbers (or some kind of error, if these numbers can’t be computed—e.g. if p₀ > p).

I tried to experiment with BINOM.INV(), but I never got anything even close to the right solution with this function.

**MrShorty** · 06-04-2020, 12:42 PM

I tried to experiment with BINOM.INV(), but I never got anything even close to the right solution with this function.

As I said, that is because BINOM.INV() is inverting the cumulative distribution function, not the probability density function. If you know what your target values for the PDF translate to on the CDF, then you can easily use BINOM.INV(). I don't know how to translate the PDF to CDF in a case like this, so I don't know how doable that approach would be.

My solution to the "I could use Solver but I don't want to use Solver" scenario is to program my own "root-finding" algorithm. I have a tutorial here: https://www.excelforum.com/tips-and-...ind-roots.html The main first question is whether to do it purely in the spreadsheet (with iteration enabled) as shown in the first several examples. The final example (post #9) shows how this might be done in a VBA user-defined function.

How do you want to proceed?

**-=-** · 06-05-2020, 05:38 AM

Seems I found the solution.

Let n be the number of trials, π₀ be the reference probability. (I denote it with π to avoid confusing with p-value that will be mentioned later.) Let p₀ be the threshold probability from my original post. Then a dynamic array of all values for which the distribution is no greater than p₀ can be generated by the formula

= FILTER(SEQUENCE(n + 1, 1, 0), BINOM.DIST(SEQUENCE(n + 1, 1, 0), n, π₀, FALSE) <= p₀)

This works with Excel versions that support dynamic arrays and corresponding functions like FILTER and SEQUENCE.

But finding these values was not an end in itself for me; this was in fact needed to calculate an exact binomial p-value for two-sided proportion test: k is the observed number of successes, π = k/n is the sample proportion, and the hypotheses are:

H₀: π = π₀
H₁: π ≠ π₀

So the p-value for this test can be calculated by the formula shown at this screenshot, and the corresponding Excel file is also attached.

binomial_p-value.png