Formula for binomial threshold values

**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