Using the Binomial Distribution function in excel

**Gadam** · 09-10-2008, 05:44 PM

Hi everyone,

I have posted this question on another forum but hoped that I may be able to get a different perspective from other people.

I have been working on a problem that requires being able to calculate the chances of any winnng a series of coin tosses with a weighted coin.

The coin has for arguments sake a 65% / 35% chance of landing heads or tails and to win the game you need to hit your side 9 times.

Can you use excel to calculate things like: If heads takes the lead 1-0, what does that mean the its overall percentage chance of winning? If the score is 7-3 to tails, what are the chances of either side gaining victory? etc, etc.

I thought that there would be a way of setting up a spreadsheet that listed the goal (first to 9, 12, 15 or whatever) and below this the percentage chance of heads and the percentage chance of tails. Then somewhere would be a box to enter the current score (0-0, 3-5, 1-1 or whatever) and beside this the current chance of heads or tails gaining victory.

I've tried setting up something like this but am a bit of a dufus when it comes to knowing how, where and when to use the different functions of excel.

Would anyone be able to give me a hand in how to set up something like this?

Thanks in advance for any help.

Here is the link to the same question on another board http://www.mrexcel.com/forum/showthread.php?t=340808

**Gadam** · 09-27-2008, 06:24 AM

Hi, my threads seems to have slid off the board so I was hoping that if I brought it back up I might be able to get a bit of input from somebody.

**shg** · 09-28-2008, 04:20 PM

I don't think this can be calculated in closed-form solution.

Please Login or Register  to view this content.

For your example on the other board (with a coin biased to flip heads 65% of the time, what's the probability of accumulating 7 heads before 9 tails?),

=pWin(65%, 7, 9) returns 99.62%.

**Gadam** · 10-16-2008, 05:09 PM

WOW!!!!

Thank you so much for your help SHG, this looks awesome!

**shg** · 10-16-2008, 05:17 PM

Thought you'd died ...

To be clear, the posted code computes the probability of flipping (for the example) 7 more heads than tails.

The probability of flipping 7 heads before 9 tails would require changing the code -- you only need to simulate 16 flips total.

**Gadam** · 10-16-2008, 05:28 PM

err....

I've just realised that I don't really know what to do with this bit of code. Could someone enlighten me on how to apply it to a spreadsheet?

**shg** · 10-16-2008, 05:30 PM

1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Close the VBE to return to Excel

**Gadam** · 10-16-2008, 05:38 PM

Thanks a lot SHG. Without wanting to test your patience - how do I get it to perform the calculations for me now? I've followed the steps that you listed but now I'm back on a blank worksheet.

I've never used the VBA side of things before you see.

Once again - really appreciate your help mate. I can't thank you enough

**shg** · 10-16-2008, 05:53 PM

See the example usage in post #3.

**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,

Please Login or Register  to view this content.

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))