PDA

View Full Version : Can't calculate long recursion



alex009988
07-31-2019, 09:49 AM
Hello I'm trying to implement numerical methods in Excel. The first step is to create equation function and it seems work.
But when m is more than 170 I get the error type mismatch


Function PoissonEqs(p As Double, m As Integer)
If m = 0 Then
PoissonEqs = Exp(-p)
Else
PoissonEqs = Exp(m * Log(p) - p - Log(Application.Gamma(m + 1))) + PoissonEqs(p, m - 1)
End If
End Function
Sub test()
Dim a As Double
Debug.Print PoissonEqs(193.063, 170)
End Sub

For m 171 p is 194.125.
I need to get m as 1000 as the least.
Regards,
Alex

Paul_Hossler
07-31-2019, 12:14 PM
Try making 'm' a Long instead of an Integer

alex009988
08-01-2019, 05:55 AM
Thank you for your reply. I've tried m as long and m as double. Unfortunatelly, the same error appears to me.

Paul_Hossler
08-01-2019, 07:01 AM
It looks like you're exceeding the capacity of a Double when you hit 172






Double (double-precision floating-point) variables (https://docs.microsoft.com/en-us/office/vba/language/glossary/vbe-glossary#double-data-type) are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from:




-1.79769313486231E308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232E308 for positive values



24724

alex009988
08-01-2019, 08:49 AM
Thank you for your reply. I have another question.
In secand numerical Method I have on denominator something like this


Sub test2()
Dim a As Double
a = (Exp(-200) - 0.005) - (Exp(-183) - 0.005)
Debug.Print a
End Sub

The problem is that I have to work with f(x1)=0,005->f(x)=f(x1)-0.005=0 so it's rounded and with the result of that devision by zero got appeared.
Is there a way to keep off that problem? Thanks in Advance!

Paul_Hossler
08-02-2019, 06:54 AM
No way I know since you're dealing with some very small floating point numbers

I have seen some high percision add on packs that you might investigate, but I have no experience with them

I believe that they use strings to emulate numbers and have their own add, subtract, etc. functions