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:


[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]

p(Heads)​

[/td][td]

50%​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

n(Heads)​

[/td][td]

6​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

Tosses​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

6​

[/td][td]

6​

[/td][td]

0.0%​

[/td][td]B6: =1 - BINOMDIST(nH, Tosses, PH, TRUE)[/td][/tr]
[tr][td]

7​

[/td][td]

7​

[/td][td]

0.8%​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

8​

[/td][td]

3.5%​

[/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

9​

[/td][td]

9.0%​

[/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

10​

[/td][td]

17.2%​

[/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

11​

[/td][td]

27.4%​

[/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

12​

[/td][td]

38.7%​

[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

13​

[/td][td]

50.0%​

[/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

14​

[/td][td]

60.5%​

[/td][td][/td][/tr]
[tr][td]

15​

[/td][td]

15​

[/td][td]

69.6%​

[/td][td][/td][/tr]
[tr][td]

16​

[/td][td]

16​

[/td][td]

77.3%​

[/td][td][/td][/tr]
[tr][td]

17​

[/td][td]

17​

[/td][td]

83.4%​

[/td][td][/td][/tr]
[tr][td]

18​

[/td][td]

18​

[/td][td]

88.1%​

[/td][td][/td][/tr]
[tr][td]

19​

[/td][td]

19​

[/td][td]

91.6%​

[/td][td][/td][/tr]
[tr][td]

20​

[/td][td]

20​

[/td][td]

94.2%​

[/td][td][/td][/tr]
[tr][td]

21​

[/td][td]

21​

[/td][td]

96.1%​

[/td][td][/td][/tr]
[tr][td]

22​

[/td][td]

22​

[/td][td]

97.4%​

[/td][td][/td][/tr]
[/table]

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:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]

pHeads​

[/td][td]

55%​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

nTrials​

[/td][td]

21​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

nHeads​

[/td][td]

8​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

Event​

[/td][td]

Prob​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]

Exactly nHeads​

[/td][td]

5.3%​

[/td][td]B6: =BINOM.DIST(nHeads, nTrials, pHeads, FALSE)[/td][/tr]
[tr][td]

7​

[/td][td]

nHeads or fewer​

[/td][td]

9.1%​

[/td][td]B7: =BINOM.DIST(nHeads, nTrials, pHeads, TRUE)[/td][/tr]
[tr][td]

8​

[/td][td]

More than nHeads​

[/td][td]

90.9%​

[/td][td]B8: =1 - BINOM.DIST(nHeads, nTrials, pHeads, TRUE)[/td][/tr]
[tr][td]

9​

[/td][td]

nHeads or more​

[/td][td]

96.2%​

[/td][td]B9: =BINOM.DIST(nTrials - nHeads, nTrials, 1 - pHeads, TRUE)[/td][/tr]
[/table]

Can you sort it from there?