how to solve equations in excel that involve iterations?

[Guest]

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?

 

[Mike Middleton]

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

 

[Guest]

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.

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

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?

 

[Mike Middleton]

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

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.

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

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?

 

[Dana DeLouis]

Hi. Just some thoughts.
With multiple square roots, it brings to mind multiple solutions. Also,
note that it a>e, 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...