I have trouble getting a solver call to work, but another call does
work. Let me show you what I have so far. On a form I have two solver
calls, the first one works but the second doesn't. There is no error
(at least none that I can recall) , it's just that the solver doesn't
run, meaning the cell $A$150 has the same value at the end as it had in
the beginning. Even debugging the code doesn't give me any clues. I
calculated everything by hand and I am pretty sure that there is a
valid solution for this equation.
What am I doing wrong?
The first one( this one works)

'//Iteration algorithm for determening the inner dia meter of the
expanded liner by using the Solver

'//Creating a temporary changing cell
Sheets("outputExpansion").Cells(150, 1)
=(Sheets("InputValues").Cells(13, 2) - 2 *
Sheets("InputValues").Cells(14, 2)) * 25.4

'//Creating a temporary target cell
If m_rho = (-2 / 3) Then

Sheets("outputExpansion").Cells(149, 1).Formula = "=$A$150 +
(2* InputValues!B14 * 25.4) * ($A$150 / ((InputValues!B13 - 2
*InputValues!B14))* 25.4) ^ (-2 / 3) - ((InputValues!B4 - 2 *
InputValues!B2) * 25.4)"

Else

Sheets("outputExpansion").Cells(149, 1).Formula = "=$A$150 +
(2* InputValues!B14 * 25.4) * ($A$150 / ((InputValues!B13 - 2
*InputValues!B14))* 25.4) ^ (-1) - ((InputValues!B4 - 2
*InputValues!B2) * 25.4)"

End If

'//Actual Solver procedure
Sheets("outputExpansion").Select
solver.Auto_open
SolverOk SetCell:="$A$149", MaxMinVal:=3,
ValueOf:="0",ByChange:="$A$150"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
solver.Auto_close

'//The inner dia meter of the liner after the Solver procedure
de = Sheets("outputExpansion").Cells(150, 1)

'//Delete the temporary cells
Sheets("outputExpansion").Cells(150, 1).Delete
Sheets("outputExpansion").Cells(149, 1).Delete

'//================================================== ==========================

Now the second one, where I need to know the value of $A$150, B9 =
0.003, B7= 0.19 , B3 = 0.26, B2 = 0.28

'// Maximum expansion ratio for rho = -2/3

Sheets("outputExpansion").Cells(150, 1) = 0.0001

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

'//Actual Solver procedure
Sheets("outputExpansion").Select
solver.Auto_open
SolverOk SetCell:="$A$149", MaxMinVal:=3,
ValueOf:="0",ByChange:="$A$150"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
solver.Auto_close

'//The inner dia meter of the liner after the Solver procedure
delta_exp_ratio = Sheets("outputExpansion").Cells(150, 1)

'//Delete the temporary cells
Sheets("outputExpansion").Cells(150, 1).Delete
Sheets("outputExpansion").Cells(149, 1).Delete

'//================================================== =======

Is there anyone who can help me???

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

You're right, when you say that tha 25.4 gets cancelled out, but it is
a conversion constant (inch to millimeter) and I' ve already removed
it. I'm going to try your idea and get back to you.

xyfix

Dana DeLouis schreef:

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

Hi. If the second equation is correct, and given your data, a math program
shows that there are two solutions for A150 that can make your equation
equal to Zero.
A150 ~ .1001 & A150 ~ .624
You may have to adjust Solver's options for Precision, Tolerance, and
Convergence.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
You're right, when you say that tha 25.4 gets cancelled out, but it is
a conversion constant (inch to millimeter) and I' ve already removed
it. I'm going to try your idea and get back to you.

xyfix

Dana DeLouis schreef:

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

Just an idea. Sometimes, you can get more accuracy by using Newton's
Equation directly in your code. This isn't fancy, but I was thinking
something like this...

Sub TestIt()
Debug.Print FX(1)
Debug.Print FX(0)
End Sub

Returns:
0.624031260377961
0.10013561506815

Function FX(Guess) As Double

Dim B9, B7, B3, B2, K
Dim Num, Den
Dim J As Long
Dim X As Double

B2 = 0.28
B3 = 0.26
B7 = 0.19
B9 = 0.003
K = 1.52765618
X = Guess

For J = 1 To 12

Num=Exp((B7-(B9+X)/K))*((B9+X)/(B7*K))^B7-B3/B2

Den=((B7*((B9+X)/(B7*K))^(B7+1)*(B7*K-X-B9))/_
(Exp(((B9-B7*K+X)/K))*(B9+X)^2))

X = X - Num / Den
Next J
FX = X
End Function

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"Dana DeLouis" wrote in message
...
Hi. If the second equation is correct, and given your data, a math
program shows that there are two solutions for A150 that can make your
equation equal to Zero.
A150 ~ .1001 & A150 ~ .624
You may have to adjust Solver's options for Precision, Tolerance, and
Convergence.

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
You're right, when you say that tha 25.4 gets cancelled out, but it is
a conversion constant (inch to millimeter) and I' ve already removed
it. I'm going to try your idea and get back to you.

xyfix

Dana DeLouis schreef:

Hi. I don't have a solution, just an idea.
Although not based on anything, I try to avoid Solver problems that gets
data from other sheets. I would try to move everything to one sheet.
I would try to use Range Names if possible. Note that 2.718^x = Exp(x).
I used a Constant k to represent 1.527...
It appears to me that 25.4 just cancels out. Is the equation correct?

See if this is a little easier to read.

=Exp((B7-(A150+B9)/k))*((A150+B9)/(B7*k))^B7-B3/B2

Range("Target").Formula = ...etc

vs...

Sheets("outputExpansion").Cells(149, 1).Formula = "=((InputValues!B9
+$A$150)/(1.52765618*
InputValues!B7))^InputValues!B7*(2.71828182845904^ (InputValues!B7
-($A$150 + InputValues!B9)/1.52765618))- ((InputValues!B3
*25.4)/(InputValues!B2 * 25.4)) "

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
ups.com...
Is there anyone who can help me???

Thank you Dana DeLouis,

your suggestion works perfectly. Somehow the solver couldn't get it as
it should do.

Sorry for the late response, but I was on a well deserved vacation.

xyfix.

I tried to apply the newton-Raphson method (solver is not working OK)
that is described by Dan DeLouis for another equation, but I can't
seem to get it right. The Newton-Raphson method tells us that x1 =
x0 - f(x0)/ f '(x0). The derivative of f(x) with respect to x is the
problem here. Here is what i got so far and can anybody say if the Den
is OK

Dim B2, B4, B13, B14
Dim Num, Den
Dim Q As Long
Dim K

B13 = Sheets("InputValues").Cells(13, 2)
B14 = Sheets("InputValues").Cells(14, 2)
B4 = Sheets("InputValues").Cells(4, 2)
B2 = Sheets("InputValues").Cells(2, 2)
K = -(2/3)
de = 0.00001

For Q = 1 To 12

Num = de + (2 * B14) * (de / (B13 - 2 * B14)) ^ (K) - (B4
- 2 * B2)

Den = 1 + (2 * B14 * K * (de / (B13 - 2 * B14)) ^ (K -
1)) /(B13 - 2 * B14)

de = de - (Num / Den)

Next Q

Hi. It looks to me that the Denominator (Den) is ok.

K = -(2/3)
(de / (B13 - 2 * B14)) ^ (K)

Don't know what errors you have, but my "guess" is that the problem is with
the negative value of k given above.
If the above equation were a^k, then just note that if 'a' were negative,
then Excel can't take the power. It's a complex value.
For example, on the worksheet,
=POWER(-8,-(2/3))
Returns the "#Num!" Error.
Is this the issue you have with your test data?

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


"xyfix" wrote in message
oups.com...
I tried to apply the newton-Raphson method (solver is not working OK)
that is described by Dan DeLouis for another equation, but I can't
seem to get it right. The Newton-Raphson method tells us that x1 =
x0 - f(x0)/ f '(x0). The derivative of f(x) with respect to x is the
problem here. Here is what i got so far and can anybody say if the Den
is OK

Dim B2, B4, B13, B14
Dim Num, Den
Dim Q As Long
Dim K

B13 = Sheets("InputValues").Cells(13, 2)
B14 = Sheets("InputValues").Cells(14, 2)
B4 = Sheets("InputValues").Cells(4, 2)
B2 = Sheets("InputValues").Cells(2, 2)
K = -(2/3)
de = 0.00001

For Q = 1 To 12

Num = de + (2 * B14) * (de / (B13 - 2 * B14)) ^ (K) - (B4
- 2 * B2)

Den = 1 + (2 * B14 * K * (de / (B13 - 2 * B14)) ^ (K -
1)) /(B13 - 2 * B14)

de = de - (Num / Den)

Next Q

It's been a while that Dana Delouis mailed me with the perfect
solution, but I forgot to put it on the board. But as people would
say "better late than never." :-)

Mail from Dana DeLouis:

Hi. If interested, here are just a couple of small ideas for your
code.
It seems to work find. However, it's not designed to be fancy to
catch the
errors when you take (-x)^(-2/3).
One idea here is to factor out a common term from the numerator and
denominator.

Sub TestIt()
Dim B2, B4, B13, B14
Dim Num, Den, de
Dim k As Double

Dim t As Double 'Temporary Variable
Dim N As Long ' Count of Loops
Dim C As Collection 'Check for Duplicates

Set C = New Collection

B2 = 0.28
B4 = 6.65
B13 = 4.25
B14 = 0.25
k = -(2 / 3)
de = 0.00001 'Or 10

On Error Resume Next
Do
N = N + 1
t = 2 * B14 * (de / (B13 - 2 * B14)) ^ k
Num = 2 * B2 - B4 + de + t
Den = 1 + (k * t) / de
de = de - (Num / Den)
C.Add de, CStr(de)
Debug.Print N; FormatNumber(de, 15)
Loop While Err.Number = 0 And N <= 30

End Sub