Extracting a Root

[Carolca98]

Hi, I'm new to the Forun, and although I don't consider myself a power user by any stretch of the imagination, I do sometimes get into some tricky little problems.

I have an equation in which the variable I want to solve for is under a square root on both sides of the equation. The equation cannot be reduced or factored any further than it is. My HP 48GX has a root facility that makes short work of the problem, but I have not been able to figure out how to make Excel do it.

Someone mentioned to me that there might be a way to run an approximation routine through 'N' interations and come up something close, but I haven't figured out how to do that either.

Can anyone out there help??

Thanks in advance.

 

[Yogi Anand]

Hi Carolca98:

Welcome to MrExcel Board!

How about posting what you are trying to accomplish -- so viewers on the Board can see what you are working with!

 

[Aaron Blood]

We know this returns a square root.
=SQRT(25)

Another way to express it is:
=25^(1/2)

They didn't create a special function for cube or higher roots, rather they expect people to understand the above relationship and be able to apply it to higher order roots.

For instance the cube root of 155:
=155^(1/3)

The nth root of x:
=x^(1/n)


If I've stated the obvious, please disregard and followup with more info.

 

[Carolca98]

Hi Carolca98:

Welcome to MrExcel Board!

How about posting what you are trying to accomplish -- so viewers on the Board can see what you are working with!

Good idea. Here goes:

1/SQRT(f) = 2*LOG10((E/3.7D)+(2.51/R*SQRT(f)))

Solve for 'f'.

As you can see, this is not just deriving a square or cube root, but extracting a root from both sides of an unfactorable equation.

Tricky eh?

I'd really appreciate any help.

 

[Jay Petrulis]

Hi,

Just to be clear, the second part on the log part of the equation is

(2.51/R) * Sqrt(f)

and not

2.51 / (R*Sqrt(f))

correct? I am assuming that you wrote it as desired, but if not, easily changed.

You can rewrite your equation as follows

1. Multiply both sides by Sqrt(f) to get

1 = 2*Sqrt(f) * Log10 ((E/3.7D)+(2.51/R*SQRT(f)))

which can be rewritten as...

1 = [Log10 ((E/3.7D)+(2.51/R*SQRT(f)))] ^ 2*Sqrt(f)

2. Remove the Log10 function by taking 10^xxx on each side to get...

10 = ((E/3.7D)+(2.51/R*SQRT(f))) ^ 2*Sqrt(f)

3. Subtract 10 from each side

0 = ((E/3.7D)+(2.51/R*SQRT(f))) ^ 2*Sqrt(f) - 10

4. Set up your worksheet using this rearranged function (I am assuming that you have a handle that the domain of f is > 0, D<>0, R<> 0, etc.)

5. Use the Solver, by setting the value to zero.

6. If Solver bombs, give the f reference cell a seed value and redo. In my sample, a seed of zero failed, but a seed of 1 worked.

Book7
	A	B	C	D	E
1	E=	3
2	D=	1
3	R=	2
4
5	f=	1.674133
6
7	Result	3.9E-07
8
9
10
11
12
Sheet1

 

[Carolca98]

Hi,

Just to be clear, the second part on the log part of the equation is

(2.51/R) * Sqrt(f)

and not

2.51 / (R*Sqrt(f))

correct? I am assuming that you wrote it as desired, but if not, easily changed.

No. Sorry for the confusion. It's the second expression: 2.51 / (R*Sqrt(f)), which makes it harder.

 

[SIXTH SENSE]

1/SQRT(f) = 2*LOG10((E/3.7D)+(2.51/R*SQRT(f)))

Solve for 'f'.

As you can see, this is not just deriving a square or cube root, but extracting a root from both sides of an unfactorable equation.

Tricky eh?

I'd really appreciate any help.

hi!
care to post values of E,R and D?
Your Equation seems not convergent.
when I tried to transpose all in one side and equate to
zero, and run a bisection method of root finding, it finds nothing
between 0 an 100000.
The values of this constant are critical, i think!

 

[Carolca98]

hi!
care to post values of E,R and D?
Your Equation seems not convergent.
when I tried to transpose all in one side and equate to
zero, and run a bisection method of root finding, it finds nothing
between 0 an 100000.
The values of this constant are critical, i think!

I guess I should have realized the importance of being more specific. As an architect, I sometimes get a little fuzzy around the edges. Let me try again.

The formula is: 1/SQRT(f) = -2*LOG10((E/(3.7*D))+(2.51/(R*SQRT(f))))
Reasonalbe values of the variables include:
E=.00015
D=6
R=233,227
solving for 'f' should yield a value of approximately .017

Hope this helps.

Again, thanks for all your help.

 

[SIXTH SENSE]

hi!
ok!

Try this!
and check the value.
The answer is 0.0153988368573056



<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Log10(X)
    Log10 = Log(X) / Log(10#)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Bisection()
<SPAN style="color:#00007F">Dim</SPAN> high <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> low <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> mid <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Double</SPAN>
E = 0.00015
R = 233227
D = 6
Acc = 0.0000000001 <SPAN style="color:#007F00">'ACCURACY</SPAN>
a = 0.000000000001 <SPAN style="color:#007F00">'STARTING RANGE</SPAN>
b = 10             <SPAN style="color:#007F00">'ENDING RANGE</SPAN>
mid = (a + b) / 2
f_mid = 1 / Sqr(mid) + 2 * Log10((E / (3.7 * D)) + 2.51 / (R * Sqr(mid)))
f_b = 1 / Sqr(b) + 2 * Log10((E / (3.7 * D)) + 2.51 / (R * Sqr(b)))
<SPAN style="color:#00007F">While</SPAN> Abs(f_mid) > Acc
        <SPAN style="color:#00007F">If</SPAN> f_mid * f_b < 0 <SPAN style="color:#00007F">Then</SPAN>
            a = mid
        <SPAN style="color:#00007F">Else</SPAN>
            b = mid
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
mid = (a + b) / 2
f_mid = 1 / Sqr(mid) + 2 * Log10((E / (3.7 * D)) + 2.51 / (R * Sqr(mid)))
f_b = 1 / Sqr(b) + 2 * Log10(E / (3.7 * D) + 2.51 / (R * Sqr(b)))
i = i + 1
<SPAN style="color:#00007F">Wend</SPAN>
MsgBox "one root is approximately " & Chr(13) & " root= " & Format(mid, "00.000000000000000") & Chr(13) & "with error of " & Chr(13) & "error= " & Acc
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Jay Petrulis]

Hi SixthSense,

Nice job! Mucho kudos on the good work here.

Should the structure of the equation remain the same, and only the E,R, and D variables will change (not the constants), this would lend itself well to a UDF rather than a macro.

Function Bisection(E As Double, R As Double, D As Double, _
                   LowerBound As Double, UpperBound As Double, _
                   Acc As Double) As Double


Dim mid As Double
Dim a As Double, b As Double
Dim f_mid As Double, f_b As Double


a = LowerBound 'STARTING RANGE
b = UpperBound  'ENDING RANGE

mid = (a + b) / 2
f_mid = 1 / Sqr(mid) + 2 * Log10((E / (3.7 * D)) + 2.51 / (R * Sqr(mid)))
f_b = 1 / Sqr(b) + 2 * Log10((E / (3.7 * D)) + 2.51 / (R * Sqr(b)))
While Abs(f_mid) > Acc
        If f_mid * f_b< 0 Then
            a = mid
        Else
            b = mid
        End If
mid = (a + b) / 2
f_mid = 1 / Sqr(mid) + 2 * Log10((E / (3.7 * D)) + 2.51 / (R * Sqr(mid)))
f_b = 1 / Sqr(b) + 2 * Log10(E / (3.7 * D) + 2.51 / (R * Sqr(b)))
Wend
Bisection = mid
End Function

This will let you set the error tolerance as well.

Call as something like...

=Bisection(_E_,_R_,_D_,0,10,1E-12)

Also, I took out the unused variables in your code. I know why they were there -- to either break out and exit or to anneal a diverging iteration (from testing on the original, incorrect equation). I would find the error checking on something like this, assuming that the equation can change, to be quite tedious. Any insights you can offer if you've done this before? How about if the equation turns chaotic or such and doesn't converge to the tolerance level? What is the best way to handle this?

For a more generalized range of equations, how would you know the starting range? 0 and 1 would work, and at some points, there is a slight (not meaningful) difference in the final result depending on the bounds. At others, which cause errors, the bad selection is apparent. What is the workaround here?

Bisection.xls
	A	B	C	D	E
1	E	0.00015		Leftsideofequation	8.026306594
2	D	6		Rightsideofequation	8.026306594
3	R	223200		Difference	0
4
5	F	0.0155227445174305
6
7	Lower	0
8	Upper	10
9	Tolerance	1.00E-14
10
Sheet1