how to solve equations in excel that involve iterations?
 

The equation has one unknown, but appear on both sides of the equation:

A+B/2 = SQRT(C/D) * [(E - A)^(3/2)] / [(A - B)^(1/2)]

THe only unknown that need to be solved is A. B, C, D, and E are known
variables. How to solve?

how to solve equations in excel that involve iterations?
 

san2000 -

Maybe you could use Goal Seek (to change A by trying to set the difference
between the left and right sides of your equation to the value zero).

For example, using the values B=2, C=3, D=4, and E=5, I entered the
following in cell B1:

=A1+2/2-SQRT(3/4)*((5-A1)^(3/2))/((A1-2)^(1/2))

Then I entered a tentative value of 3 in cell A1.

Then I chose Tools | Goal Seek, and used Set Cell B1, To Value 0 (zero), and
By Changing Cell A1.

For more precision, choose Tools | Options | Calculation, and enter a
smaller value for Maximum Change. You do not need to check the Iterations
box.

- Mike
http://www.mikemiddleton.com

"san2000" wrote in message
...
The equation has one unknown, but appear on both sides of the equation:

A+B/2 = SQRT(C/D) * [(E - A)^(3/2)] / [(A - B)^(1/2)]

THe only unknown that need to be solved is A. B, C, D, and E are known
variables. How to solve?

how to solve equations in excel that involve iterations?
 

Thanks Mike. I use Goal Seek, but find out that it can only apply one cell
at a time. Is there a way to make the process faster? Will Macro helps in
this situation? I'm not familar with Macro at all. Any help would be great.
Thanks in advance.

"Mike Middleton" wrote:

san2000 -

Maybe you could use Goal Seek (to change A by trying to set the difference
between the left and right sides of your equation to the value zero).

For example, using the values B=2, C=3, D=4, and E=5, I entered the
following in cell B1:

=A1+2/2-SQRT(3/4)*((5-A1)^(3/2))/((A1-2)^(1/2))

Then I entered a tentative value of 3 in cell A1.

Then I chose Tools | Goal Seek, and used Set Cell B1, To Value 0 (zero), and
By Changing Cell A1.

For more precision, choose Tools | Options | Calculation, and enter a
smaller value for Maximum Change. You do not need to check the Iterations
box.

- Mike
http://www.mikemiddleton.com

"san2000" wrote in message
...
The equation has one unknown, but appear on both sides of the equation:

A+B/2 = SQRT(C/D) * [(E - A)^(3/2)] / [(A - B)^(1/2)]

THe only unknown that need to be solved is A. B, C, D, and E are known
variables. How to solve?

how to solve equations in excel that involve iterations?
 

san2000 -

Your description of your problem specifically stated there was only one
unknown, so Goal Seek is appropriate. If you have several unknowns, Excel's
Solver may be more appropriate.

In general, a macro (VBA) will not be faster than Excel's built-in features.

- Mike

"san2000" wrote in message
...
Thanks Mike. I use Goal Seek, but find out that it can only apply one
cell
at a time. Is there a way to make the process faster? Will Macro helps
in
this situation? I'm not familar with Macro at all. Any help would be
great.
Thanks in advance.

"Mike Middleton" wrote:

san2000 -

Maybe you could use Goal Seek (to change A by trying to set the
difference
between the left and right sides of your equation to the value zero).

For example, using the values B=2, C=3, D=4, and E=5, I entered the
following in cell B1:

=A1+2/2-SQRT(3/4)*((5-A1)^(3/2))/((A1-2)^(1/2))

Then I entered a tentative value of 3 in cell A1.

Then I chose Tools | Goal Seek, and used Set Cell B1, To Value 0 (zero),
and
By Changing Cell A1.

For more precision, choose Tools | Options | Calculation, and enter a
smaller value for Maximum Change. You do not need to check the Iterations
box.

- Mike
http://www.mikemiddleton.com

"san2000" wrote in message
...
The equation has one unknown, but appear on both sides of the equation:

A+B/2 = SQRT(C/D) * [(E - A)^(3/2)] / [(A - B)^(1/2)]

THe only unknown that need to be solved is A. B, C, D, and E are known
variables. How to solve?

how to solve equations in excel that involve iterations?
 

Hi. Just some thoughts.
With multiple square roots, it brings to mind multiple solutions. Also,
note that it ae, or a<b, then you will be working with Complex numbers.
(Sqrt of negative numbers). Does this constraint hold? b<a<e? If C is the
opposite sign of D, you will have a sqrt of a negative number as well.
One additional method could be the Newton Equation. Goal Seek works great,
but can be limited in its accuracy. However, you can loop a few more times
in your own function.
This isn't fancy, and it doesn't have much error checking. Here's a simple
example using Mike's numbers...

Sub TestIt()
Debug.Print Fx(2, 3, 4, 5)
End Sub

Function Fx(b, c, d, e)
Dim K1
Dim K2
Dim a
Dim Num
Dim Den
Dim Last

K1 = b / 2
K2 = Sqr(c / d)
Last = 0
a = (b + e) / 2 'Middle
Do While Last < a
Last = a
Num = a + K1 - ((K2 * (e - a) ^ (3 / 2)) / Sqr(a - b))

Den = ((Sqr(e - a) * (2 * a - 3 * b + e) * K2) / _
(a - b) ^ (3 / 2) + 2) / 2

a = a - Num / Den
Debug.Print a

If a e Then a = e
If a < b Then a = b + 0.00001
Loop
End Function

Returns a = 2.68510380165928
which checks...

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


"san2000" wrote in message
...
The equation has one unknown, but appear on both sides of the equation:

A+B/2 = SQRT(C/D) * [(E - A)^(3/2)] / [(A - B)^(1/2)]

THe only unknown that need to be solved is A. B, C, D, and E are known
variables. How to solve?