[bines]

Hello everyone !

Do not know how to upload a file ,
How do I implement this functionality, can anyone give an example with an Excel file?



Option Explicit

Public Function BivariatePoisson(x As Integer, y As Integer, lambda1 As Double, lambda2 As Double, lambda3 As Double) As Double
Dim min As Integer
Dim quotient As Double
Dim sum As Double
Dim i As Integer

If x < y Then
min = x
Else
min = y
End If
quotient = lambda3 / (lambda1 * lambda2)
sum = 0
For i = 0 To min
sum = sum + WorksheetFunction.Combin(x, i) * WorksheetFunction.Combin(y, i) * WorksheetFunction.Fact(i) * (quotient ^ i)
Next i

BivariatePoisson = Exp(-(lambda1 + lambda2 + lambda3)) * ((lambda1 ^ x) / WorksheetFunction.Fact(x)) * ((lambda2 ^ y) / WorksheetFunction.Fact(y)) * sum
End Function

Public Function BivariatePoissonWithDiagonalInflation(x As Integer, y As Integer, lambda1 As Double, lambda2 As Double, lambda3 As Double, inflation As Double, geometricP As Double) As Double
Dim bp As Double
Dim result As Double

bp = BivariatePoisson(x, y, lambda1, lambda2, lambda3)
result = (1# - inflation) * bp

If (x = y) Then
result = result + inflation * ((1 - geometricP) ^ x) * geometricP
End If

BivariatePoissonWithDiagonalInflation = result
End Function


Thank you

[daringly]

not VBA, but you can convert this pretty easily. Once you have in VBA, it is easy to set up as macro.

function bivarpoisson(m1,m2,m3: real; k1,k2:longint):real;
var part1,part2:real;
l1, mink:longint;
begin
part1:=exp(-m1-m2-m3)*powerfn(m1,k1)*powerfn(m2,k2)/factorial(k1)/factorial(k2);
if k1>k2 then mink:=k2 else mink:=k1;
part2:=0;
for l1:=0 to mink do
part2:=part2+choose(k1,l1)*choose(k2,l1) *factorial(l1)*
powerfn(m3/m2/m1,l1);
bivarpoisson:=part1*part2;
end;

[bines]

How to calculate the geometric ?

I know

lambda1 =goal Expected HOME
lambda2 =goal Expected Visitor
lambda3 =Covariance
inflation=The addition to a draw
Can anyone help, how do I calculate the geometric ?

Thank you !

[daringly]

m3 is lamda3 -- the covariance. The greater the covariance, the more often you will predict draws compared to a straight Poisson distribution.

[sergenyalcin]

not VBA, but you can convert this pretty easily. Once you have in VBA, it is easy to set up as macro.

function bivarpoisson(m1,m2,m3: real; k1,k2:longint):real;
var part1,part2:real;
l1, mink:longint;
begin
part1:=exp(-m1-m2-m3)*powerfn(m1,k1)*powerfn(m2,k2)/factorial(k1)/factorial(k2);
if k1>k2 then mink:=k2 else mink:=k1;
part2:=0;
for l1:=0 to mink do
part2:=part2+choose(k1,l1)*choose(k2,l1) *factorial(l1)*
powerfn(m3/m2/m1,l1);
bivarpoisson:=part1*part2;
end;

Hello to everyone
I did not do this for VBA. Is there anyone to help me?

[sergenyalcin]

Hello again,
I would like to use Bivariate Poisson with Diagonal Inflation in Macro for excel.
The person who helped
I will get a small gift from me