Estimating the square root of using Microsoft Excel?

Question

I need to use the function q^2+n/2q where n is the number that I want to estimate the square root of. Apparently this was Newton's method of finding square roots, but I have no idea what to input for a q value in my excel sheet. Any idea?

Answer 1

I think your function is; you missed out the parans

f(q) = (q^2+n)/2q

The theory is that the solution to the equation f(q) = q is sqrt(n).

This is an ideal problem to solve in Excel. Your first guess of q can be anything, answer will eventually converge to sqrt(n).

In Excel let cell A1 hold the value of n, say 10.

The in cell A2 type in your initial guess for sqrt(n), so just type in 1.

In cell A3 type in "=(A2^2 + $A$1)/(2*A2)" without the double quotes

Now grab the bottom right hand corner of cell A3 and drag it right down to row 30 or 40 such that the formula copies.

You'll find that the formula converges fairly quickly. Now change your initial guess (cell A2) and see how it effects the convergence.

Notes:

As your initial guess in Excel you can use a number very close to 0 such as 0.00000000001 and it'll still converge, but dont use 0 as this causes a divide by 0 and blows up the formula!

If you use negative numbers you'll still converge but this time to the negative value of the square root.

Challenge:
Find a formula that finds the cube root using the above procedure.

Further Challenge:
Find a formula that finds the kth root using the above procedure.

Answer 2

It doesn't matter much as long as you choose a reasonable value. If you want the square root of 54, for example, you might start with 7 or 8. If you want to keep things simple, always start with q=1. It will work, but will take a little longer to converge.

(An unreasonable value would be 0 or a negative number or 0.000001.)

If you were doing the calculations with pen and paper, you'd want to save time by starting with a value close to the square root. If Excel is doing the work, it doesn't matter if it takes more steps to get to a good answer.

By the way, your expression needs parentheses:
(q^2 + n) / (2*q)
If you put a formula into Excel without the correct parentheses, it won't work.

(Let me make this a little more general: When you apply Newton's method to the function x^2-N, it doesn't matter much what you pick as an initial guess. For other functions, however, the method might not work unless you make a good initial guess. It all depends on how complex the function is.)

Answer 3

Try this

http://mitpress.mit.edu/sicp/chapter1/node9.html