Need MS Excel solution if possible
I have a girlfriend who likes to eat cookies but I am not a very good Baker. The probability that she likes my baked cookies is 2/3 (Let's say she kisses me if she likes a cookie). I want to be 95% confident that she is able to get at least 5 cookies that she likes. I want to find out at least how many cookies shall I bake? If the numbers are small, the calculation by hand is easy. For example, if I bake only 5 Cookies, the probability that she will like all 5 cookies is only 0.26, but If I bake 6 cookies, then the probability that she likes at least 5 cookies increases to 0.35, if I bake 7 it increases to 0.57 and so on.
On Excel, I am using the below formula to find the probability of at least 5 likes out of 7 trials
=BINOM.DIST(7, 7, B3, TRUE) - BINOM.DIST(4, 7, B3, TRUE)
The first term is calculating the cumulative frequency that she likes at most 7, which is 100%. The second term is calculating the cumulative frequency that she likes at most 4. The difference would then give that she likes 5 cookies, 6 cookies, or 7 cookies.
However, if I have a target like 95%, then one way is to increase the value 7 one by one and I can find that at n = 11, the probability increases to 0.96. But how to do it without using manual increment and checking?
You can compute the desired probability with the formula
=1-BINOM.DIST(4,A2,2/3,1)directly on the spreadsheet. Note that when $n=11$, the probability exceeds 0.95, which gives your desired answer. Using Excel allows you to copy and paste formulas without doing "manual increments".