It would seem that if the Double Data Type is the largest I can have, then I
have to re-think my entire aproach.
The function doesnt seem to breakdown in Excel, and I have discovered that I
can call the function in Excel from Access, so I may have to resort to that,
but that would mean that both are running at the same time
"Michael Hudston" wrote:
> I have written this VBA Script to basically calculate the number of spares
> based on the Poisson Culumative Distribution. However after a while it
> breaks down as the variable FCT goes into overflow. - The number hits in the
> region of 1.17E+254 I know this is a massive number but then that is
> indicitive of the equation as in theory it could go to infinity
>
> I am wondering what my next step is, and if there is a solution or not.
>
> The Function I have written is below, and as a guide the following set of
> values can be used (the first set works, the second doesnt)
>
> 1st Set
>
> Probability = 0.9
> Lambda = 156.967232876712
>
> 2nd Set
>
> Probability = 0.9
> Lambda = 131.205435114504
> .
>
> Code
>
> Public Function InvPoisson(ByVal Probability As Double, ByVal Lambda As
> Double) As Long
>
> ' This function is designed to return the number of spares required when
> fed with the Availiability, and Mean.
> ' Written By Michael J Hudston
>
> ' Ensure all figures are reset before we start
>
> Dim Fct As Double
>
> Count_A = 1
> Complete = 0
> Prob = 0
> SumProb = 0
> Fct = 1
> StockOutRisk = 1 - Probability
> i = 0
> InvPoisson = 0
>
> ' Now do the majical maths stuff
>
> Do
>
> ' Set the Start point for the Culumative Probability Calculation for
> this run.
>
> SumProb = 0
> Fct = 1
>
> ' Carry out the Culumative Probability Caculation for this run.
>
> For i = 0 To Count_A
>
> ' Do stuff for the Factorial.
>
> If i = 0 Then
> Fct = 1
> Else
> Fct = Fct * i
> End If
>
> Prob = (Exp(-1 * Lambda) * Lambda ^ i) / Fct
> SumProb = SumProb + Prob
>
> Next
>
> ' If the Culumative Probability is greater than 1 - the Stock Out
> Risk then you dont need to continue
> ' so set the complete flag
>
> If Complete = 0 Then
> If SumProb > 1 - StockOutRisk Then
> InvPoisson = Count_A
> Complete = 1
> Else
> ' Otherwise add one to the spares count and run the
> Culumative calculation again.
> Count_A = Count_A + 1
> End If
>
> End If
>
> Loop Until Complete = 1
>
> ' Repeat the loop until the complete flag has been set
> ' (Ie 1 - Stock Out Risk is less than the Culumative Probability.
>
> End Function
>
|
Similar Threads
