Calculating a growing formula

Hi,

I'm having a little problem. I'm trying to calculate a formula which grows for every calculation I must make. I always have to use the 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". I 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 of "m". So, I was wondering if there was a way to simplify those calculations....

THanks

> 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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"bambinos83" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I'm having a little problem. I'm trying to calculate a formula which
> grows for every calculation I must make. I always have to use the
> 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". I
> 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 of
> "m". So, I was wondering if there was a way to simplify those
> calculations....
>
> THanks
>
>
> --
> bambinos83
> ------------------------------------------------------------------------
> bambinos83's Profile:
> http://www.excelforum.com/member.php...o&userid=32589
> View this thread: http://www.excelforum.com/showthread...hreadid=556589
>

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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

<< Forget that one! >>

> First few solutions are:
>
> 0 0.051293
> 1 0.355362
> 2 0.817691
> 3 1.366318
> 4 1.970150
> 5 2.613015
>
>
> "bambinos83" <[email protected]>
> wrote in message
> news:[email protected]...
>>
>> Hi,
>>
>> I'm having a little problem. I'm trying to calculate a formula which
>> grows for every calculation I must make. I always have to use the
>> 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". I
>> 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 of
>> "m". So, I was wondering if there was a way to simplify those
>> calculations....
>>
>> THanks
>>
>>
>> --
>> bambinos83
>> ------------------------------------------------------------------------
>> bambinos83's Profile:
>> http://www.excelforum.com/member.php...o&userid=32589
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=556589

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

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

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"bambinos83" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for your time Dana.
>
> A few questions though. On your last post, you sent another form of
> "SolveForM(x) Function". This one can replace the other one in the
> 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.
>
>
> --
> bambinos83
> ------------------------------------------------------------------------
> bambinos83's Profile:
> http://www.excelforum.com/member.php...o&userid=32589
> View this thread: http://www.excelforum.com/showthread...hreadid=556589
>

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

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


--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"bambinos83" <[email protected]> wrote
in message news:[email protected]...
>
> Wow... this is great... thanks a lot Dana
>
> 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!!
>
>
>
> --
> bambinos83
> ------------------------------------------------------------------------
> bambinos83's Profile:
> http://www.excelforum.com/member.php...o&userid=32589
> View this thread: http://www.excelforum.com/showthread...hreadid=556589
>

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

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

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

> 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
Windows XP, Office 2003


"bambinos83" <[email protected]> wrote
in message news:[email protected]...
>
> 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
> ------------------------------------------------------------------------
> bambinos83's Profile:
> http://www.excelforum.com/member.php...o&userid=32589
> View this thread: http://www.excelforum.com/showthread...hreadid=556589
>

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. :>)
--
Dana DeLouis
<snip>