I need help with a difficult problem

#1
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

(P(A))^(i)*(P(B))^(i+V)*(P(C))^(N-2*i-V)*N!/(i!*(i+V)!*(N-2*i-V)!)


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

SUMFUNCTION1 = Sum1

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)
 

katxt

New Member
#2
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.