0

The problem is as follows:

Let's say there will be "X" independent events in my binomial tree, and I know the probability of all "X" events being a "1" or a "0" outcome. How can I use excel to calculate the probability of every possible sum total? (addition of all 1s and 0s, ie X, X-1, X-2, ..., 1, 0)

For example I have 4 events in my series (eventually I want up to 17) with probabilities of 0.36, 0.09, 0.91, 0.36.

How do I get excel to spit out:

P(score=4) = .033 P(score=3) = ... P(score=2) = ... P(score=1) = ... P(score=0) = .01

Thank you for your help!

Michael Rundell
  • 11
  • 1

1 Answers1

0

Binomial tree calculation is iterative process: calculate current state from previous state probabilities.

Let's state n has probabilities Pn(x) for each x, where x is sum total. Then state n+1 has probabilities:

Pn+1(x) = Pn(x) * (1-p) + Pn(x-1) * p

On state n+1 probability to have sum total x is equal to sum of two probabilities:

  • probability to have sum total x on state n multiplied by probability to have zero on event: (1-p)
  • probability to have sum total x-1 on state n multiplied by probability to have one on event: p

So, you start from state 0, where P0(0) = 1 and can expand such tree on any number of events:

binomial tree

Look at formula for N6 for clarification

Kozyr
  • 201
  • 1
  • 7