Using the Binomial Distribution function in excel

**Gadam** · 10-16-2008, 06:02 PM

Thats amazing, thank you very much SHG

**shg** · 10-16-2008, 06:24 PM

You're welcome, thanks for the feedback.

**Gadam** · 10-17-2008, 10:55 AM

Hi SHG,

I've gone over the excellent piece of code that you contributed a few times and think that there is a small anomoly but cannot figure out exactly where.

I set up a small tool to use the function and think that the percentage split for heads and tails is being amplified too much when you seperate them.

When they are split 50/50 the results look correct but any other weighting looks wrong when I've experimented. I'll give a few examples:

50/50 split and the goal is first to reach 5 successes (best of 9):

Chances of either side winning (50%/50%)

2-1 (57%/43%)
3-1 (67%/33%)
4-1 (80%/20%)
3-2 (50%/40%)
4-2 (75%/25%)
4-3 (67%/33%)

Like I said before these look to be bang on in terms of reflecting the chances of either side winning.

In a 60/40 split with the same goal however I get these results:

Chances of either side winning (88%/12%) ???

2-1 (85%/15%)
3-1 (88%/12%) ???
4-1 (92%/8%)
3-2 (81%/19%)
4-2 (88%/12%) ???
4-3 (79%/21%)

With a 60/40 split the tails (40% side) has to get to a score of 0-4 before it is considered more likely to win which surely cannot be correct.

Have I applied the piece of code that you made me wrong or is there a simple solution the problem? I have attached a copy of the spreadsheet that I was using on here (the cells using the formula are in D13 and D14)

Once again, thanks for all of your help up until now I really appreciate it.

Thanks

Gadam

**shg** · 10-17-2008, 11:26 AM

As I tried to explain (and apparently not well), that's not what the code calculates.

Start flipping a coin, and in a column, write down the running total, adding one for each heads and subtracting 1 for each tails. This is a random walk; if 9 tosses went

H-H-T-H-T-T-T-H-H

the column would read

+1, +2, +1, +2, +1, 0, -1, 0, +1.

The code calculates the probability that you get to (say) +3 before getting to (say) -5. Capische?

The code keeps flipping until the probability of one event or the other occurring (e.g., reaching +3 or -5) is 99.99% (which converges quickly with a biased coin), and then scales the numbers upward to total 100%.

Simulating a finite number of flips (e.g., best of 9) could be done with a slight modification.

Edit: In fact, for 'best of' trials, you can just use the BINOMDIST function.

**shg** · 10-17-2008, 12:00 PM

... for example,

      ---A--- --B--- -----C----- ----D-----
  1   # Flips nHeads prob(Heads) Heads Wins
  2      3      2        60%       64.8%   
  3      4      3        60%       47.5%   
  4      5      4        60%       33.7%   
  5      5      3        60%       68.3%   
  6      6      4        60%       54.4%   
  7      7      4        60%       71.0%

Row 2 is interpreted as, "In three flips of a coin biased to flip heads 60% of the time, what's the probability of flipping two or more heads? (64.8%)

The array formula in D2 and copied down is

=SUM(BINOMDIST( ROW(INDIRECT(B2 + 1 & ":" & A2 + 1) ) - 1, A2, C2, FALSE))