Calculating a growing formula

[bambinos83]

Hi,

I'm having a little problem. I'm trying to calculate a formula whic
grows for every calculation I must make. I always have to use th
solver so it takes a while to get it done. Here is my problem.

For x that goes from 1 to 30 (all integers), I want to evaluate "m".
also want the result of the equation to be 0.95 so here how it goes:

For x=0: 0.95 = exp(-m) -> m = 0.051
For x=1: 0.95 = exp(-m) * (1+m) -> m = 0.355
For x=2: 0.95 = exp(-m) * (1+m+m²/2) -> m = 0.818
For x=3: 0.95 = exp(-m) * (1+m+m²/2+m³/6) -> m = 1.366
For x=4: 0.95 = exp(-m) * (1+m+m²/2+m³/6+(m^4)/24)
and so on...

As you may notice, the denominator is the factorial of the exponent o
"m". So, I was wondering if there was a way to simplify thos
calculations....

THank

 

[Dana DeLouis]

I always have to use the

solver so it takes a while to get it done
For x that goes from 1 to 30 (all integers), I want to evaluate "m".

Hi. Here's one idea. To keep the main loop simple, I set the loops to 10.
You only need about 6 loops.

Sub TestIt()
Dim j
For j = 0 To 30
Debug.Print j; FormatNumber(SolveForM(j), 6)
Next j
End Sub

Function SolveForM(x)
'//=======================
'// By: Dana DeLouis
'//=======================

Dim z As Double
Dim j As Long

With WorksheetFunction
z = x
If z = 0 Then z = 0.001
For j = 1 To 10
z = z + Exp(z) * .Power(z, -x) * (Gamma(x + 1, z) - 0.95 * .Fact(x))
Next j
End With
SolveForM = z
End Function

Function Gamma(Alpha, z)
With WorksheetFunction
Gamma = Exp(.GammaLn(Alpha)) * (1 - .GammaDist(z, Alpha, 1, True))
End With
End Function

First few solutions are:

0 0.051293
1 0.355362
2 0.817691
3 1.366318
4 1.970150
5 2.613015

 

[Dana DeLouis]

For x that goes from 1 to 30 (all integers), I want to evaluate "m".

Oops! I see there is the same calculation within a loop. Bad Programming
!!!
Here's an update:

Sub TestIt()
Dim x
For x = 0 To 30
Debug.Print x; ": "; FormatNumber(SolveForM(x), 6)
Next x
End Sub

Function SolveForM(x)
'// By: Dana DeLouis

Dim g As Double
Dim k As Double
Dim j As Long

With WorksheetFunction
g = .Max(x, 0.001) ' Best (g)uess
k = 0.95 * .Fact(x) ' Constant
For j = 1 To 10
g = g + Exp(g) * .Power(g, -x) * (Gamma(x + 1, g) - k)
Next j
End With
SolveForM = g
End Function

Function Gamma(Alpha, z)
With WorksheetFunction
Gamma = Exp(.GammaLn(Alpha)) * (1 - .GammaDist(z, Alpha, 1, True))
End With
End Function

 

[Dana DeLouis]

Another option is to combine the two functions:

Function SolveForM(x)
Dim g As Double
Dim f As Double
Dim gd As Double
Dim j As Long

With WorksheetFunction
g = .Max(x, 0.001)
f = .Fact(x)
For j = 1 To 10
gd = .GammaDist(g, x + 1, 1, True) - 0.05
g = g - (Exp(g) * .Power(g, -x) * gd * f)
Next j
End With
SolveForM = g
End Function

 

[bambinos83]

Thanks for your time Dana.

A few questions though. On your last post, you sent another form o
"SolveForM(x) Function". This one can replace the other one in th
entire code I assume?

I looked at it and when I run it, the results are not printed anywhere
I was wondering how I to know if it really worked. Thanks again

 

[Dana DeLouis]

Hi. This will put it on the worksheet, and also in the immediate window.
Not sure what you have set up.
You can pull up the Immediate window in vba by hitting Ctrl+G
As a side note, because it's a function, you can use it on a worksheet also.
=SolveForM(A1)
Also note that this version is only good to about 8-9 digits, as Excel's
"GammaDist" function is only accurate to that many digits as well.
We note that (1+m+m²/2+m³/6+(m^4)/24)... is the Taylor expansion of Exp(m)
to your 'x terms. Solving with a m^30 term is hard, so that's why I
suggested this iterative approach.

Sub TestIt()
Dim x As Long
For x = 1 To 30
Cells(x, 1) = FormatNumber(SolveForM(x), 6)
Debug.Print x; ": "; FormatNumber(SolveForM(x), 6)
Next x
Columns("A:A").NumberFormat = "#0.000000"
End Sub

Function SolveForM(x)
Dim g As Double
Dim f As Double
Dim gd As Double
Dim j As Long

With WorksheetFunction
g = .Max(x, 0.001)
f = .Fact(x)
For j = 1 To 10
gd = .GammaDist(g, x + 1, 1, True) - 0.05
g = g - (Exp(g) * .Power(g, -x) * gd * f)
Next j
End With
SolveForM = g
End Function

 

[bambinos83]

Wow... this is great... thanks a lot Donna

The 0.05 is my probability (1-0.95). If I wanted to change my
probability by affecting it a cell in the worksheet, what would I have
to write to replace the 0.05 in the line of the gamma distribution?

Let's say that the cell is B1.

The VBA is awesome and works great though. Thank you for your help!!

 

[Dana DeLouis]

Glad it works! Thanks for the feedback.

Looks like we can add a second argument to the function. This seems to work
for 0.9.
On the worksheet, it might look something like this, with B1 holding 0.9

=SolveForM(A1,B1)

Kind of an interesting Limit question as x approaches infinity.
Your series terms get closer to Exp(m), so your Right Hand Side tends
towards
Exp(-m) * Exp(m), which approaches 1.

Sub TestIt()
Dim x As Long
Dim p As Double 'Probability
p = 0.9

For x = 1 To 30
Cells(x, 1) = FormatNumber(SolveForM(x, p), 6)
Debug.Print x; ": "; FormatNumber(SolveForM(x, p), 6)
Next x
Columns("A:A").NumberFormat = "#0.000000"
End Sub

Function SolveForM(x, p)
Dim g As Double
Dim f As Double
Dim gd As Double
Dim j As Long

With WorksheetFunction
g = .Max(x, 0.001)
f = .Fact(x)
For j = 1 To 10
gd = .GammaDist(g, x + 1, 1, True) + p - 1
g = g - (Exp(g) * .Power(g, -x) * gd * f)
Next j
End With
SolveForM = g
End Function

 

[bambinos83]

Thanks once again. I think you didn't get what I was saying but it
doesn't matter, I fixed it. It was really easy in fact!! So here is the
final code. I must thank you once again for all you've done on this
one.

Sub TestIt()
Dim x As Long
For x = 0 To 110
Cells(x + 4, 1) = FormatNumber(SolveForM(x), 6)
Debug.Print x; ": "; FormatNumber(SolveForM(x), 6)
Next x
Range("A1:A40").NumberFormat = "#0.000000"
End Sub

Function SolveForM(x)
Dim g As Double
Dim f As Double
Dim gd As Double
Dim j As Long
Dim a As Double

a = Cells(1, 2)
With WorksheetFunction
g = .Max(x, 0.001)
f = .Fact(x)
For j = 1 To 10
gd = .GammaDist(g, x + 1, 1, True) - a
g = g - (Exp(g) * .Power(g, -x) * gd * f)
Next j
End With
SolveForM = g
End Function

 

[bambinos83]

Thanks once again. I think you didn't get what I was saying but i
doesn't matter, I fixed it. It was really easy in fact!! So here is th
final code. I must thank you once again for all you've done on thi
one.

Sub TestIt()
Dim x As Long
For x = 0 To 110
Cells(x + 4, 1) = FormatNumber(SolveForM(x), 6)
Debug.Print x; ": "; FormatNumber(SolveForM(x), 6)
Next x
Range("A1:A40").NumberFormat = "#0.000000"
End Sub

Function SolveForM(x)
Dim g As Double
Dim f As Double
Dim gd As Double
Dim j As Long
Dim a As Double

a = Cells(1, 2)
With WorksheetFunction
g = .Max(x, 0.001)
f = .Fact(x)
For j = 1 To 10
gd = .GammaDist(g, x + 1, 1, True) - a
g = g - (Exp(g) * .Power(g, -x) * gd * f)
Next j
End With
SolveForM = g
End Functio

 

[bambinos83]

Thanks once again. I think you didn't get what I was saying but it
doesn't matter, I fixed it. It was really easy in fact!! So here is the
final code. I must thank you once again for all you've done on this
one.

Sub TestIt()
Dim x As Long
For x = 0 To 110
Cells(x + 4, 1) = FormatNumber(SolveForM(x), 6)
Debug.Print x; ": "; FormatNumber(SolveForM(x), 6)
Next x
Range("A1:A40").NumberFormat = "#0.000000"
End Sub

Function SolveForM(x)
Dim g As Double
Dim f As Double
Dim gd As Double
Dim j As Long
Dim a As Double

a = Cells(1, 2)
With WorksheetFunction
g = .Max(x, 0.001)
f = .Fact(x)
For j = 1 To 10
gd = .GammaDist(g, x + 1, 1, True) - a
g = g - (Exp(g) * .Power(g, -x) * gd * f)
Next j
End With
SolveForM = g
End Function

 

[Dana DeLouis]

a = Cells(1, 2)

Hi. Glad it's working! Just want to point out something, as I'm not sure
of your data in B1.
If I put 0.95 into B1, I don't get the same answers as your test data.
I think you meant to use the following
a = 1 - Cells(1, 2)

With the above change, it works as expected. However, you data in B1 may
already be set up for this.
Again, just pointing it out.

As a side note, with x equal to 110, using factorial is a large number.
However, the final answer is still pretty good. The final answer appears to
be accurate to about 6-7 digits. Not too bad for such large numbers & using
Excel's "GammaDist" function.

 

[Dana DeLouis]

Oh...Never mind. I see that you are doing it correctly.
I just noticed that you are using 0.05 & 0.10, instead of 0.95 & 0.90, as
I hit the send button.
(That's what I had set up)
So great! Glad it's working. :>)