+ Reply to Thread
Results 1 to 2 of 2

Thread: I need help with a difficult problem

  1. #1
    Points: 4, Level: 1
    Level completed: 7%, Points required for next Level: 46

    Thanked 0 Times in 0 Posts

    I need help with a difficult problem

    Here's the problem:

    Each trial can have 3 outcomes with different chances of each, let's say P(A) = .2, P(B) = .15, and P(C) = .65. I want to find the chances that the total occurrences of A and B are always V results apart in a total of N trials, in math terms V =Occurrences of A - Occurrences of B

    Sum from i = 0 to 24


    This is my solution, and it seems to work in wolfram alpha, but i'm trying to adapt it into an excel function and it's returning numbers far larger than 1, so i'm hoping someone could check out my math to make sure i got the equation right, in which case i'll dig into the code more

    in case any of you know VBA this is how i'm coding the function currently.

    Public Function SUMFUNCTION1(A As Double, B As Double, C As Double, Trials As Integer, Difference As Integer) As Double
    Dim i As Integer, j As Integer, Sum1 As Double

    j = (Trials - Difference)

    If j Mod (2) = 1 Then
    j = (Trials - Difference - 1) / 2
    j = (Trials - Difference) / 2
    End If

    For i = 0 To j
    Sum1 = Sum1 + (((A ^ (i)) * (B ^ (i + Difference)) * ((C) ^ (Trials - (2 * i) - Difference)) * Application.WorksheetFunction.Fact(Trials)) / (Application.WorksheetFunction.Fact(i) * Application.WorksheetFunction.Fact(i + Differnce) * Application.WorksheetFunction.Fact(Trials - (2 * i) - Difference)))
    Next i


    End Function

    I hope you guys can help, i'm quite stuck at this point (i suspect it's an issue with the code though)

  2. #2
    Points: 1,974, Level: 26
    Level completed: 74%, Points required for next Level: 26

    New Zealand
    Thanked 48 Times in 47 Posts

    Re: I need help with a difficult problem

    I assume that when you say "always V results apart in a total of N trials" you mean "exactly V results apart after a total of N trials", and that V = A - B is positive when you have more A outcomes and V is negative when you have more B outcomes.
    I'll leave the code to you, but here is an Excel spreadsheet way of calculating P(N,V) using a recursive formula which you might find easy to code into VBA.
    If you have V =A-B after N trials, then after N-1 trials you must have had either V-1 and got an A, V and got a C, or V+1 and got a B
    so P(N,V) = P(N-1,V-1)*a + P(N-1,V)*c+P(N-1,V+1)*b. To start this off, P(0,V)=0 except for P(0,0)=1.

    You can use this to check your calculations.
    Attached Files

+ Reply to Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Advertise on Talk Stats