![](/i/fill.gif) |
![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
If a weighted coin is tossed N times, count the times it lands heads as H.
The probability of landing heads is P(H).
I assume the expected value of H is N*P(H).
But how do I find out what range of H is X% likely to happen? For example,
I want to be able to say that in 90% of cases H will be in the range (A,B).
What I've done so far is for 0<=H<=N calculated (N choose H)/P, where P is
the total permutations of N items in N. This should give me the probability
of exactly H heads coming up, yes? I can keep a cumulative total of these
probabilites for each H, and then figure out the range of H between (say)
10% and 90%. But it seems a bit long-winded, I can't figure out how to
calculate the range directly. Also Excel can't figure out the permutations
when N is bigger than 150 or so, I want it to work for higher values of N.
Any ideas? Hopefully it's something obvious!
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
scott wrote:
> If a weighted coin is tossed N times, count the times it lands heads as
> H. The probability of landing heads is P(H).
I presume by this you explicitly mean that P(H) /= 50%?
> I assume the expected value of H is N*P(H).
Yes. That would be the definition of "probability". (Assuming all the
trails are independent.)
> But how do I find out what range of H is X% likely to happen? For
> example, I want to be able to say that in 90% of cases H will be in the
> range (A,B).
Now you're talking about probability distributions. If you can decide
what probability distribution this experiment has, you can [potentially]
directly compute the result you're after.
Wikipedia informs me that what you're looking for is a "binomial
distribution". According to this, we have the probability of getting
exactly K heads as
(N choose K) * P(H)^K * (1 - P(H))^(N-K)
More usefully for you, the cumulative probability - the probability of K
heads OR LESS, is given by
I[1 - P(H)] (N - K, N + K)
where I[x](a, b) represents the regularised incomplete beta function,
http://en.wikipedia.org/wiki/Regularized_incomplete_beta_function
Unless Excel has a built-in function to compute I(), I would suggest it
would be simpler and easier to directly compute the sum over the
individual probabilities. (Especially since you want the probability for
a value between K1 and K2, not between K1 and 0.)
You can also compute the cumulative probability by the integral
(N - K) (N choose K) integral[0 .. 1-P(H)] t^(N-K-1) (1 - t)^K dt
But I'm guessing Excel can't do that.
Apparently you can also estimate the maximum probability by
exp( -2 * (N * P(H) - K)^2 / N )
(The true cumulative probability is less than or equal to this.
Apparently for P(H) = 50% it's quite a good approximation, but I'm not
sure for other values.)
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Invisible wrote:
> More usefully for you, the cumulative probability - the probability of K
> heads OR LESS, is given by
>
> I[1 - P(H)] (N - K, N + K)
>
> where I[x](a, b) represents the regularised incomplete beta function,
>
> http://en.wikipedia.org/wiki/Regularized_incomplete_beta_function
http://reference.wolfram.com/mathematica/ref/BetaRegularized.html
So Wolfram Alpha will compute it, for example.
But that doesn't matter:
http://office.microsoft.com/en-us/excel/HP052090051033.aspx
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
> Wikipedia informs me that what you're looking for is a "binomial
> distribution".
Thanks - that was the key I needed!
> According to this, we have the probability of getting exactly K heads as
>
> (N choose K) * P(H)^K * (1 - P(H))^(N-K)
That *is* useful, at least using this formula I get correct results,
although it's still a bit clumsy.
I will continue with Excel now trying to make it a bit neater.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
scott wrote:
>> Wikipedia informs me that what you're looking for is a "binomial
>> distribution".
>
> Thanks - that was the key I needed!
>
>> According to this, we have the probability of getting exactly K heads as
>>
>> (N choose K) * P(H)^K * (1 - P(H))^(N-K)
>
> That *is* useful, at least using this formula I get correct results,
> although it's still a bit clumsy.
>
> I will continue with Excel now trying to make it a bit neater.
See my other post; Excel has a function to directly compute a binomial
distribution, apparently. (And cumulatively, if you wish.)
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
> See my other post; Excel has a function to directly compute a binomial
> distribution, apparently. (And cumulatively, if you wish.)
I need the inverse function though, I want to tell it the cumulative
probability (eg 10% and 90%) and it tell me how many successful trials that
relates to. There seems to be no BINOMDISTINV function.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
scott wrote:
>> See my other post; Excel has a function to directly compute a binomial
>> distribution, apparently. (And cumulatively, if you wish.)
>
> I need the inverse function though, I want to tell it the cumulative
> probability (eg 10% and 90%) and it tell me how many successful trials
> that relates to. There seems to be no BINOMDISTINV function.
Wikipedia claims that for large N, the binomial distribution
approximates the normal distribution. (If you plot the normal
distribution with expected value N * P(H) and varience N * P(H) * (1 -
P(H)).)
This approximation works best for large N and for P(H) near to 50%.
I presume it's relatively easy to work out the inverse function for a
normal distribution...
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
> Wikipedia claims that for large N, the binomial distribution approximates
> the normal distribution.
Yes, it seems my parameter values fit in ok for this approximation to be
pretty accurate.
> I presume it's relatively easy to work out the inverse function for a
> normal distribution...
Excel has a built in function to do this, so this looks like the way to go.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
>> Wikipedia claims that for large N, the binomial distribution
>> approximates the normal distribution.
>
> Yes, it seems my parameter values fit in ok for this approximation to be
> pretty accurate.
Central Limit Theorum FTW! :-D
>> I presume it's relatively easy to work out the inverse function for a
>> normal distribution...
>
> Excel has a built in function to do this, so this looks like the way to go.
Cool.
So are you *really* trying to estimate coin flips? Or is there some more
complex problem you're trying to solve here?
Also... I'm impressed that I managed to find an actual solution to this
using nothing but Wipikedia and an incomplete understanding of probability.
PS. Apparently Wolfram Alpha can plot all these distributions for you if
you type in something suggestive like "binomial distribution, n=100, p=0.2".
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
> So are you *really* trying to estimate coin flips? Or is there some more
> complex problem you're trying to solve here?
Not really more complex, just a game that is played repeatedly and you have
a certain % chance of winning (say 57%). I just want to know if you play
10,100, 1000 times what is the *likely* number of wins in some kind of worst
case and best case scenario. ie it's obviously possible to win every single
game, but that is *really* unlikely, I want to know how many you'd win with
90% or 99% confidence.
Post a reply to this message
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |