# Extracting a Root

#### Carolca98

##### New Member
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. ### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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!

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)

Yogi Anand said:
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.

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
ABCDE
1E=3
2D=1
3R=2
4
5f=1.674133
6
7Result3.9E-07
8
9
10
11
12
Sheet1

Jay Petrulis said:
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.

Carolca98 said:
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?
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!

SIXTH SENSE said:
hi!
care to post values of E,R and D?
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.

hi!
ok!

Try this!
and check the value.

<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>

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.

Code:
``````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
ABCDE
1E0.00015Leftsideofequation8.026306594
2D6Rightsideofequation8.026306594
3R223200Difference0
4
5F0.0155227445174305
6
7Lower0
8Upper10
9Tolerance1.00E-14
10
Sheet1

Replies
11
Views
308
Replies
3
Views
1K
Replies
1
Views
980
Replies
0
Views
958
Replies
0
Views
532

### Forum statistics

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back