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

--
bambinos8
-----------------------------------------------------------------------
bambinos83's Profile: http://www.excelforum.com/member.php...fo&userid=3258
View this thread: http://www.excelforum.com/showthread.php?threadid=55658

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 a

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" wrote
in message ...

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 a

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


"bambinos83"
wrote in message
...

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

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

--
bambinos8
-----------------------------------------------------------------------
bambinos83's Profile: http://www.excelforum.com/member.php...fo&userid=3258
View this thread: http://www.excelforum.com/showthread.php?threadid=55658

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" wrote
in message ...

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


--
bambinos83
------------------------------------------------------------------------
bambinos83's Profile: http://www.excelforum.com/member.php...o&userid=32589
View this thread: http://www.excelforum.com/showthread...hreadid=556589

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" wrote
in message ...

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


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

--
bambinos8
-----------------------------------------------------------------------
bambinos83's Profile: http://www.excelforum.com/member.php...fo&userid=3258
View this thread: http://www.excelforum.com/showthread.php?threadid=55658

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

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" wrote
in message ...

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