Using the Binomial Distribution function in excel

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

Function pWin(p As Double, nH As Long, nT As Long) As Variant
    ' Returns the probability that a coin with
    ' probability p of heads will accumulate
    ' nH excess heads before nT excess tails
    Dim q       As Double   ' 1-p
    Dim adPrb() As Double  ' array of probabilities
    Dim i       As Long     ' index to array
    Dim pH      As Double   ' probability of Heads winning
    Dim pT      As Double   ' probability of Tails winning
    
    Dim dH      As Double   ' rate at which pH is increasing
    Dim dT      As Double   ' rate at which pT is increasing
    
    Dim dPrb    As Double   ' temporary variable
    Dim iOdd    As Long     ' odd/even roll
    Dim nToss   As Long     ' tosses made
    
    If nH < 1 Then Exit Function
    If nT < 1 Then Exit Function

    q = 1# - p
    ReDim adPrb(-nT To nH)
    adPrb(0) = 1#

    ' loop until the sum of probabilities is close to 1
    Do While pH + pT < 0.99999
        iOdd = (iOdd + 1) Mod 2
        For i = -nT + ((nT + iOdd) And 1) To nH Step 2
            dPrb = 0#
            If i > 1 - nT Then dPrb = p * adPrb(i - 1)
            If i < nH - 1 Then dPrb = q * adPrb(i + 1) + dPrb
            adPrb(i) = dPrb
            If i = -nT Then pT = pT + dPrb
            If i = nH Then pH = pH + dPrb
        Next i
        nToss = nToss + 1
    Loop
    ' scale result to 100%
    pWin = Array(pH / (pH + pT), nToss)
End Function

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%.

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.

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

You're welcome, thanks for the feedback.

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.

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