Cumulative binomial equation in Excel 2013

[xicor]

Hi All,
I am trying to solve the following formula in excel for variable "n":

All other variables will have already been calculated and I just need to solve for "n". I have tried using the different binomial functions offered by excel but none give the results I am looking for.

Particularly, I am not sure how to deal with solving for a specific variable and how excel would deal with the summation or if that function would have to be done manually.

Any help is greatly appreciated.

Thanks!

 

[shg]

Welcome to the board.

See Help for the BinomDist (or Binom.Dist) function. Post back with more specifics if you need help sorting it from there.

 

[xicor]

Welcome to the board.

See Help for the BinomDist (or Binom.Dist) function. Post back with more specifics if you need help sorting it from there.

Thanks for the reply shq.

-The Binom.Dist function has a few issues:

• The Binom.Dist functions takes number of trials as an input and this is what I am trying to solve for.
• As far as I can tell there is no way to re-arrange the inputs/outputs of this.
• The way this takes inputs I am unsure how I would couple this with the needed summation as well.

 

[shg]

Q: How many tosses of a fair coin are required to flip 6 or more heads with 95% confidence?

You could create a table and do a lookup:

Row\Col	A​	B​	C​
1​	p(Heads)​	50%​
2​	n(Heads)​	6​
3​
4​
5​	Tosses​	​	​
6​	6​	0.0%​	B6: =1 - BINOMDIST(nH, Tosses, PH, TRUE)
7​	7​	0.8%​
8​	8​	3.5%​
9​	9​	9.0%​
10​	10​	17.2%​
11​	11​	27.4%​
12​	12​	38.7%​
13​	13​	50.0%​
14​	14​	60.5%​
15​	15​	69.6%​
16​	16​	77.3%​
17​	17​	83.4%​
18​	18​	88.1%​
19​	19​	91.6%​
20​	20​	94.2%​
21​	21​	96.1%​
22​	22​	97.4%​

A: 21

Or you could do it in a single formula:

{=MATCH(TRUE, 1 - BINOMDIST(nH, ROW(INDEX($A:$A, nH):INDEX($A:$A, nH+30)), PH, TRUE) >= 95%, 0) + nH - 1}

 

[shg]

I think my example is not correct, and these are the typical formulations:

Row\Col	A​	B​	C​
1​	pHeads​	55%​
2​	nTrials​	21​
3​	nHeads​	8​
4​	​
5​	Event​	Prob​
6​	Exactly nHeads​	5.3%​	B6: =BINOM.DIST(nHeads, nTrials, pHeads, FALSE)
7​	nHeads or fewer​	9.1%​	B7: =BINOM.DIST(nHeads, nTrials, pHeads, TRUE)
8​	More than nHeads​	90.9%​	B8: =1 - BINOM.DIST(nHeads, nTrials, pHeads, TRUE)
9​	nHeads or more​	96.2%​	B9: =BINOM.DIST(nTrials - nHeads, nTrials, 1 - pHeads, TRUE)

Can you sort it from there?