Alternative to Solver needed

[Greg]

Hi all,

I have a following power equation Y = X /(1+i) where
i = a * X ^ b (a & b are constant coefficients).

Is there a way to solve for X without using Solver?

Thank you,

Greg

 

[Jonathan Rynd]

Hi all,

I have a following power equation Y = X /(1+i) where
i = a * X ^ b (a & b are constant coefficients).

Try asking on a mathematics newsgroup. They'll also be able to tell you
what is the proper excel formula. Make sure to say you're solving for X in
terms of Y.

 

[Dan E]

Greg,

You need to rearrange your formula:

Y = X/(a* X^b )

Y = 1/a* X^(1-b)

X^(1-b) = a*Y

X = (a*Y)^(1/(1-b))

Dan E

 

[Harlan Grove]

You need to rearrange your formula:

Y = X/(a* X^b )

...

And you need to reread the OP.
...

Y = X / (1 + i)
i = a * X ^ b

so

Y = X / (1 + a * X ^ b)

Presumably the OP wants to find the X value that gives rise to a particular Y
value. Clearly, Y = 0 when X = 0, Y = 1/(1+a) when X = 1, and as X tends to

1, Y = 1/(1+a) when X = 1 is an extreme point; and for b = 1, Y is always less than 1/a. So if b is greater than or equal to 1 and the Y value sought is greater than 1/(1+a) or 1/a, respectively, no solution is possible. Also, for b > 1 and Y sought is less than 1/(1+a), there are two X values, one < 1 and the other > 1, that give the Y value - which one would you want?

For the OP: would a multiple cell approach work, or are you looking for a single
cell solution?

 

[Greg]

Thanks, Harlan, for looking at this for me.

Yes, Y is known and I am trying to find X.

b is in the interval [-1,1]

Please provide both single cell solution and a multiple
one if possible.

Thanks again,
Greg

-----Original Message-----
...
...

And you need to reread the OP.

...

Y = X / (1 + i)
i = a * X ^ b

so

Y = X / (1 + a * X ^ b)

Presumably the OP wants to find the X value that gives rise to a particular Y
value. Clearly, Y = 0 when X = 0, Y = 1/(1+a) when X = 1, and as X tends to
infinity Y tends to infinity if b < 1, 1/a if b = 1, or 0 if b > 0. Also, for b

b = 1, Y is always less than 1/a. So if b is greater than
or equal to 1 and the Y value sought is greater than 1/
(1+a) or 1/a, respectively, no solution is possible. Also,
for b > 1 and Y sought is less than 1/(1+a), there are two

 

[Greg]

Could you please post a link to the math group or forward
me in the proper direction?

Thanks,

Greg

 

[Harlan Grove]

...
...

Yes, Y is known and I am trying to find X.

b is in the interval [-1,1]

Please provide both single cell solution and a multiple
one if possible.

A single cell result would require VBA. Simplistic example using binary search.


Function bs(f As String, yt As Double) As Variant
Const NMAX As Long = 100
Const YTOL As Double = 0.000000000001
Const XMAX As Double = 1000000000000#
Const LO As Long = 1, MP As Long = 2, HI As Long = 3

Dim x(LO To HI) As Double, y(LO To HI) As Double, n As Long

If Not f Like "*<>*" Then
bs = CVErr(xlErrRef)

ElseIf IsError(Evaluate(Application.Substitute(f, "<>", YTOL))) Then
bs = CVErr(xlErrValue)

Else
x(LO) = YTOL
y(LO) = Evaluate(Application.Substitute(f, "<>", x(LO)))

x(HI) = 1
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))

If yt > y(HI) Then
For n = 1 To NMAX
x(HI) = 2 * x(HI)
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))
If yt < y(HI) Or x(HI) > XMAX Then Exit For
Next n
End If

If yt < y(LO) Or yt > y(HI) Then
bs = CVErr(xlErrNum)

ElseIf Abs(y(LO) - yt) <= YTOL Then
bs = x(LO)

ElseIf Abs(y(HI) - yt) <= YTOL Then
bs = x(HI)

End If

End If

If Not IsEmpty(bs) Then Exit Function

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Do While Abs(y(MP) - yt) > YTOL
If (y(MP) - yt) * (y(LO) - yt) > 0 Then
x(LO) = x(MP)
y(LO) = y(MP)

Else
x(HI) = x(MP)
y(HI) = y(MP)

End If

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Loop

bs = x(MP)

End Function


With a, b and Y named ranges and given your functional form for Y as a function
of X, use the formula

=bs("=<>/(1+"&a&"*<>^"&b&")",Y)

to find the X value that gives the specified Y value. The first argument is a
string giving the functional form with <> denoting the independent (X) variable.


The multiple cell approach is basically the same thing. With the previous
assumptions as well as assuming A11:F1000 is empty and available for use, enter
the following.

A11:
1e-12

B11:
=(A11+C11)/2

C11:
1

D11:
=A11/(1+a*A11^b)-Y

and fill D11 right into E11:F11. Keep doubling C11 until E11 falls between D11
and F11. Then enter the following.

A12:
=IF(D11*E11>0,E11,D11)

B12:
=(A12+C12)/2

C12:
=IF(D11*E11>0,F11,E11)

and fill D11:F11 down into D12:F12. Now select A12:F12 and fill down until the
value in column E displays as 0 (zero). The corresponding column B value will be
your solution.

 

[Greg]

Looks very impressive, Harlan.

Not that I understand what you are doing here, but once I
give it a try I should get an idea.

I hope you did not spend too much of your time doing this.

Wishing you all the best in the new year, thank you for
your help.

Greg

-----Original Message-----
...
...

Yes, Y is known and I am trying to find X.

b is in the interval [-1,1]

Please provide both single cell solution and a multiple
one if possible.

A single cell result would require VBA. Simplistic example using binary search.


Function bs(f As String, yt As Double) As Variant
Const NMAX As Long = 100
Const YTOL As Double = 0.000000000001
Const XMAX As Double = 1000000000000#
Const LO As Long = 1, MP As Long = 2, HI As Long = 3

Dim x(LO To HI) As Double, y(LO To HI) As Double, n As Long

If Not f Like "*<>*" Then
bs = CVErr(xlErrRef)

ElseIf IsError(Evaluate(Application.Substitute(f, "<>", YTOL))) Then
bs = CVErr(xlErrValue)

Else
x(LO) = YTOL
y(LO) = Evaluate(Application.Substitute(f, "<>", x(LO)))

x(HI) = 1
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))

If yt > y(HI) Then
For n = 1 To NMAX
x(HI) = 2 * x(HI)
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))
If yt < y(HI) Or x(HI) > XMAX Then Exit For
Next n
End If

If yt < y(LO) Or yt > y(HI) Then
bs = CVErr(xlErrNum)

ElseIf Abs(y(LO) - yt) <= YTOL Then
bs = x(LO)

ElseIf Abs(y(HI) - yt) <= YTOL Then
bs = x(HI)

End If

End If

If Not IsEmpty(bs) Then Exit Function

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Do While Abs(y(MP) - yt) > YTOL
If (y(MP) - yt) * (y(LO) - yt) > 0 Then
x(LO) = x(MP)
y(LO) = y(MP)

Else
x(HI) = x(MP)
y(HI) = y(MP)

End If

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Loop

bs = x(MP)

End Function


With a, b and Y named ranges and given your functional form for Y as a function
of X, use the formula

=bs("=<>/(1+"&a&"*<>^"&b&")",Y)

to find the X value that gives the specified Y value. The first argument is a
string giving the functional form with <> denoting the independent (X) variable.


The multiple cell approach is basically the same thing. With the previous
assumptions as well as assuming A11:F1000 is empty and available for use, enter
the following.

A11:
1e-12

B11:
=(A11+C11)/2

C11:
1

D11:
=A11/(1+a*A11^b)-Y

and fill D11 right into E11:F11. Keep doubling C11 until E11 falls between D11
and F11. Then enter the following.

A12:
=IF(D11*E11>0,E11,D11)

B12:
=(A12+C12)/2

C12:
=IF(D11*E11>0,F11,E11)

and fill D11:F11 down into D12:F12. Now select A12:F12 and fill down until the
value in column E displays as 0 (zero). The corresponding column B value will be
your solution.

 

[Dana DeLouis]

Here's a variation of Harlan's excellent idea using a form of Newton's
method. Don't know if it would work for all your possible variables.


Sub Demo()
' For testing, find y
Dim a, b, x, y
a = 2
b = 0.5
x = 10

y = x / (1 + a * x ^ b)

'Now that you have y, try to find x
'start with a guess for x of 1

MsgBox "x is about: " & Find_x(y, a, b, 1)
End Sub

Function Find_x(y, a, b, guess)
'// Dana DeLouis

Dim NextGuess
Dim x As Double
Const small As Double = 0.00000000000001 '1e-14

NextGuess = guess

Do
x = NextGuess
NextGuess = x - ((1 + a * x ^ b) * (y - x + a * x ^ b * y)) / (-1 + a * (b -
1) * x ^ b)
Loop While Abs(x - NextGuess) > small

Find_x = Round(NextGuess, 14)
End Function


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Looks very impressive, Harlan.

Not that I understand what you are doing here, but once I
give it a try I should get an idea.

I hope you did not spend too much of your time doing this.

Wishing you all the best in the new year, thank you for
your help.

Greg

-----Original Message-----
...
...

Yes, Y is known and I am trying to find X.

b is in the interval [-1,1]

Please provide both single cell solution and a multiple
one if possible.

A single cell result would require VBA. Simplistic example using binary search.


Function bs(f As String, yt As Double) As Variant
Const NMAX As Long = 100
Const YTOL As Double = 0.000000000001
Const XMAX As Double = 1000000000000#
Const LO As Long = 1, MP As Long = 2, HI As Long = 3

Dim x(LO To HI) As Double, y(LO To HI) As Double, n As Long

If Not f Like "*<>*" Then
bs = CVErr(xlErrRef)

ElseIf IsError(Evaluate(Application.Substitute(f, "<>", YTOL))) Then
bs = CVErr(xlErrValue)

Else
x(LO) = YTOL
y(LO) = Evaluate(Application.Substitute(f, "<>", x(LO)))

x(HI) = 1
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))

If yt > y(HI) Then
For n = 1 To NMAX
x(HI) = 2 * x(HI)
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))
If yt < y(HI) Or x(HI) > XMAX Then Exit For
Next n
End If

If yt < y(LO) Or yt > y(HI) Then
bs = CVErr(xlErrNum)

ElseIf Abs(y(LO) - yt) <= YTOL Then
bs = x(LO)

ElseIf Abs(y(HI) - yt) <= YTOL Then
bs = x(HI)

End If

End If

If Not IsEmpty(bs) Then Exit Function

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Do While Abs(y(MP) - yt) > YTOL
If (y(MP) - yt) * (y(LO) - yt) > 0 Then
x(LO) = x(MP)
y(LO) = y(MP)

Else
x(HI) = x(MP)
y(HI) = y(MP)

End If

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Loop

bs = x(MP)

End Function


With a, b and Y named ranges and given your functional form for Y as a function
of X, use the formula

=bs("=<>/(1+"&a&"*<>^"&b&")",Y)

to find the X value that gives the specified Y value. The first argument is a
string giving the functional form with <> denoting the independent (X) variable.


The multiple cell approach is basically the same thing. With the previous
assumptions as well as assuming A11:F1000 is empty and available for use, enter
the following.

A11:
1e-12

B11:
=(A11+C11)/2

C11:
1

D11:
=A11/(1+a*A11^b)-Y

and fill D11 right into E11:F11. Keep doubling C11 until E11 falls between D11
and F11. Then enter the following.

A12:
=IF(D11*E11>0,E11,D11)

B12:
=(A12+C12)/2

C12:
=IF(D11*E11>0,F11,E11)

and fill D11:F11 down into D12:F12. Now select A12:F12 and fill down until the
value in column E displays as 0 (zero). The corresponding column B value will be
your solution.

 

[Greg]

Harlan,

If you ever read it again.
Say I have Y = 1000, a = 19.776, and b = -0.7476

When I tried the VBA formula, my Excel crushes. I am on
Xl97, Windows NT.

As to the multiple cell solution, I don't understand the
line "Keep doubling C11 until E11 falls between D11 and
F11."

Does this mean I should manually multiply C11 by 2 until
the value in E11 will be in between the values in D11 and
F11?
Please pardon my ignorance.

Thanks,
Greg

-----Original Message-----
...
...

Yes, Y is known and I am trying to find X.

b is in the interval [-1,1]

Please provide both single cell solution and a multiple
one if possible.

A single cell result would require VBA. Simplistic example using binary search.


Function bs(f As String, yt As Double) As Variant
Const NMAX As Long = 100
Const YTOL As Double = 0.000000000001
Const XMAX As Double = 1000000000000#
Const LO As Long = 1, MP As Long = 2, HI As Long = 3

Dim x(LO To HI) As Double, y(LO To HI) As Double, n As Long

If Not f Like "*<>*" Then
bs = CVErr(xlErrRef)

ElseIf IsError(Evaluate(Application.Substitute(f, "<>", YTOL))) Then
bs = CVErr(xlErrValue)

Else
x(LO) = YTOL
y(LO) = Evaluate(Application.Substitute(f, "<>", x(LO)))

x(HI) = 1
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))

If yt > y(HI) Then
For n = 1 To NMAX
x(HI) = 2 * x(HI)
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))
If yt < y(HI) Or x(HI) > XMAX Then Exit For
Next n
End If

If yt < y(LO) Or yt > y(HI) Then
bs = CVErr(xlErrNum)

ElseIf Abs(y(LO) - yt) <= YTOL Then
bs = x(LO)

ElseIf Abs(y(HI) - yt) <= YTOL Then
bs = x(HI)

End If

End If

If Not IsEmpty(bs) Then Exit Function

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Do While Abs(y(MP) - yt) > YTOL
If (y(MP) - yt) * (y(LO) - yt) > 0 Then
x(LO) = x(MP)
y(LO) = y(MP)

Else
x(HI) = x(MP)
y(HI) = y(MP)

End If

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Loop

bs = x(MP)

End Function


With a, b and Y named ranges and given your functional form for Y as a function
of X, use the formula

=bs("=<>/(1+"&a&"*<>^"&b&")",Y)

to find the X value that gives the specified Y value.

The
first argument is a

string giving the functional form with <> denoting the independent (X) variable.


The multiple cell approach is basically the same thing. With the previous
assumptions as well as assuming A11:F1000 is empty and available for use, enter
the following.

A11:
1e-12

B11:
=(A11+C11)/2

C11:
1

D11:
=A11/(1+a*A11^b)-Y

and fill D11 right into E11:F11. Keep doubling C11 until E11 falls between D11
and F11. Then enter the following.

A12:
=IF(D11*E11>0,E11,D11)

B12:
=(A12+C12)/2

C12:
=IF(D11*E11>0,F11,E11)

and fill D11:F11 down into D12:F12. Now select A12:F12 and fill down until the
value in column E displays as 0 (zero). The

corresponding
column B value will be

your solution.

.

 

[Dana DeLouis]

Worked ok for me...?

Sub Demo()
Dim a, b, x, y
y = 1000
a = 19.776
b = -0.7476

x = Find_x(1000, 19.776, -0.7476, 1)
'x: 1104.93940992599
'x: 1104.939409925994034065817271 <= to 28 digits
'Which compares well to the solution to 28 digits above.

' Check if it returns 1000
y = x / (1 + a * x ^ b)
'y: 1000
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Harlan,

If you ever read it again.
Say I have Y = 1000, a = 19.776, and b = -0.7476

When I tried the VBA formula, my Excel crushes. I am on
Xl97, Windows NT.

As to the multiple cell solution, I don't understand the
line "Keep doubling C11 until E11 falls between D11 and
F11."

Does this mean I should manually multiply C11 by 2 until
the value in E11 will be in between the values in D11 and
F11?
Please pardon my ignorance.

Thanks,
Greg

-----Original Message-----
...
...
Yes, Y is known and I am trying to find X.

b is in the interval [-1,1]

Please provide both single cell solution and a multiple
one if possible.

A single cell result would require VBA. Simplistic example using binary search.


Function bs(f As String, yt As Double) As Variant
Const NMAX As Long = 100
Const YTOL As Double = 0.000000000001
Const XMAX As Double = 1000000000000#
Const LO As Long = 1, MP As Long = 2, HI As Long = 3

Dim x(LO To HI) As Double, y(LO To HI) As Double, n As Long

If Not f Like "*<>*" Then
bs = CVErr(xlErrRef)

ElseIf IsError(Evaluate(Application.Substitute(f, "<>", YTOL))) Then
bs = CVErr(xlErrValue)

Else
x(LO) = YTOL
y(LO) = Evaluate(Application.Substitute(f, "<>", x(LO)))

x(HI) = 1
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))

If yt > y(HI) Then
For n = 1 To NMAX
x(HI) = 2 * x(HI)
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))
If yt < y(HI) Or x(HI) > XMAX Then Exit For
Next n
End If

If yt < y(LO) Or yt > y(HI) Then
bs = CVErr(xlErrNum)

ElseIf Abs(y(LO) - yt) <= YTOL Then
bs = x(LO)

ElseIf Abs(y(HI) - yt) <= YTOL Then
bs = x(HI)

End If

End If

If Not IsEmpty(bs) Then Exit Function

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Do While Abs(y(MP) - yt) > YTOL
If (y(MP) - yt) * (y(LO) - yt) > 0 Then
x(LO) = x(MP)
y(LO) = y(MP)

Else
x(HI) = x(MP)
y(HI) = y(MP)

End If

x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Loop

bs = x(MP)

End Function


With a, b and Y named ranges and given your functional form for Y as a function
of X, use the formula

=bs("=<>/(1+"&a&"*<>^"&b&")",Y)

to find the X value that gives the specified Y value.

The
first argument is a

string giving the functional form with <> denoting the independent (X) variable.


The multiple cell approach is basically the same thing. With the previous
assumptions as well as assuming A11:F1000 is empty and available for use, enter
the following.

A11:
1e-12

B11:
=(A11+C11)/2

C11:
1

D11:
=A11/(1+a*A11^b)-Y

and fill D11 right into E11:F11. Keep doubling C11 until E11 falls between D11
and F11. Then enter the following.

A12:
=IF(D11*E11>0,E11,D11)

B12:
=(A12+C12)/2

C12:
=IF(D11*E11>0,F11,E11)

and fill D11:F11 down into D12:F12. Now select A12:F12 and fill down until the
value in column E displays as 0 (zero). The

corresponding
column B value will be

your solution.

.

 

[Harlan Grove]

If you ever read it again.
Say I have Y = 1000, a = 19.776, and b = -0.7476

When I tried the VBA formula, my Excel crushes. I am on
Xl97, Windows NT.

The problem is my looping criteria. I should have scaled the tolerance in the Do
loop condition. Me bad. Here's a revised function that should avoid the infinite
loop that's the problem with these values in my original function.


Function bs(f As String, yt As Double) As Variant
Const NMAX As Long = 100
Const YTOL As Double = 0.000000000001
Const XMAX As Double = 1000000000000#
Const LO As Long = 1, MP As Long = 2, HI As Long = 3

Dim x(LO To HI) As Double, y(LO To HI) As Double, t As Double, n As Long

If Not f Like "*<>*" Then
bs = CVErr(xlErrRef)

ElseIf IsError(Evaluate(Application.Substitute(f, "<>", YTOL))) Then
bs = CVErr(xlErrValue)

Else
x(LO) = YTOL
y(LO) = Evaluate(Application.Substitute(f, "<>", x(LO)))

x(HI) = 1
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))

If yt > y(HI) Then
For n = 1 To NMAX
x(HI) = 2 * x(HI)
y(HI) = Evaluate(Application.Substitute(f, "<>", x(HI)))
If yt < y(HI) Or x(HI) > XMAX Then Exit For
Next n
End If

If yt < y(LO) Or yt > y(HI) Then
bs = CVErr(xlErrNum)

ElseIf Abs(y(LO) - yt) <= YTOL Then
bs = x(LO)

ElseIf Abs(y(HI) - yt) <= YTOL Then
bs = x(HI)

End If

End If


If Not IsEmpty(bs) Then Exit Function


x(MP) = (x(LO) + x(HI)) / 2
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

t = yt * YTOL
Do While Abs(y(MP) - yt) > t And n < NMAX

If (y(MP) - yt) * (y(LO) - yt) > 0 Then
x(LO) = x(MP)
y(LO) = y(MP)

Else
x(HI) = x(MP)
y(HI) = y(MP)

End If

x(MP) = (CDec(x(LO)) + CDec(x(HI))) / CDec(2)
y(MP) = Evaluate(Application.Substitute(f, "<>", x(MP)))

Loop

bs = x(MP)

End Function

 

[Harlan Grove]

Here's a variation of Harlan's excellent idea using a form of Newton's
method. Don't know if it would work for all your possible variables.

...

Yeah, it's faster, but it requires knowing the functional form of the derivative
with respect to X. You could have used the secant method, which uses only the
Y(X) function and provides much of the benefits of Newton's method.

I may or may not work on a secant method approach tonight.

 

[Greg]

Many many thanks to both of you.
It works fine now.

Just wondering how would the secant method be like
<wink>

Greg

 

[Dana DeLouis]

Just wondering how would the secant method be like

<wink>

A Secant method would be very similar to the following. This does not have
much error checking, and is not too complete. This is just to give you some
ideas to play with. There is room for improvement here. This attempts to
solve your problem with the given numbers.
I get the same answer of x= 1104.93940992599

Sub MyProgram()
Dim Ans
Dim a, b, y
a = 19.776
b = -0.7476
y = 1000
Ans = FindX(y, a, b)
MsgBox Ans
End Sub

= = = = = = = = = = = = = = = = = = = = =
You Enter your Function here...

Private Function Fx(x, a, b, y)
Fx = (x / (1 + a * x ^ b)) - y
End Function

= = = = = = = = = = = = = = = = = = = = =

Private Function FindX(y, a, b)
'// Secant Method
Dim x(1 To 20)
Dim k
'// Guessing two endpoints here...
x(1) = 1
x(2) = 2000

On Error GoTo SlopeZero
For k = 2 To 20
x(k + 1) = x(k) - Fx(x(k), a, b, y) * ((x(k) - x(k - 1)) / (Fx(x(k),
a, b, y) - Fx(x(k - 1), a, b, y)))
Next k
SlopeZero:
FindX = x(k)
End Function

 

[Dana DeLouis]

This is more along the line of how I would write it.

Sub MainProgram()
Dim Ans
Dim a, b, y
a = 19.776
b = -0.7476
y = 1000

Ans = FindX(y, a, b)
MsgBox Ans
End Sub

Private Function FindX(y, a, b)
'// Secant Method
Dim k As Long
Dim F1 As Double
Dim F2 As Double
Dim x As Variant

'// Guessing two endpoints here...
x = Array(vbNullString, 1, 2000)
On Error GoTo SlopeZero
For k = 3 To 20
x = Array(x(1), x(2), vbNullString)
F1 = (x(1) / (1 + a * x(1) ^ b)) - y
F2 = (x(0) / (1 + a * x(0) ^ b)) - y
x(2) = x(1) - F1 * ((x(1) - x(0)) / (F1 - F2))
If x(2) < 0 Then x(2) = 0
Next k
SlopeZero:
FindX = x(1)
End Function



One problem that I see is the following. This could generate a negative x
at one of the points. If "b" is say 0.5, then calling your equation could
attempt to take the square root of a negative number. This will generate an
error. Because your equation has a x^b, I have limited the x's to only
positive numbers. That is why the use of "Max." I am not sure how the
Secant method normally handles these types of problems.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

<snip>

 

[Harlan Grove]

One problem that I see is the following. This could generate a negative x
at one of the points. If "b" is say 0.5, then calling your equation could
attempt to take the square root of a negative number. This will generate an
error. Because your equation has a x^b, I have limited the x's to only
positive numbers. That is why the use of "Max." I am not sure how the
Secant method normally handles these types of problems.

Specifically with regard to the OP's function,

Y(X) = X / (1 + a * X ^ b)

Y(0) = 0 (except when b = 0). If the given Y value and a parameter are both
positive, then if it were possible to find an X value that produced the
given Y value, that X value would be positive. If either the given Y value
or the a value were negative, then I doubt the required conditions for the
applicability of either Newton's method or the secant method would be met.