20,117

8

Published

## Introduction: Spreadsheet Calculus: Newton's Method

Sometimes you need to find the roots of a function, also known as the zeroes. Sometimes finding the zeroes is pretty easy. Other times, that isn't the case. Take for example the 6th degree polynomial shown below. How do you find the zeroes to that equation? You could make a rough estimate with the graph, or you could use Newton's method.

## Step 1: How It Works

Newton's method uses tangent lines to approximate the zeroes of a function. It's difficult to explain only in word how it works, take a look at the picture below. The graph is e-x. We start at [5,148] and follow the line tangent to the curve. When the tangent line hits the x-axis (y=0) we do it again, at the x-value we found by following the tangent line. Eventually we would expect to reach a 0. In this case, we never would since e-x never touches the x-axis. It illustrates the point though.
Convince yourself that f(a)=f'(a)(a-b). f'(a) is the derivative of the function at point a. It's the slope of the function at a. If you multiply this by a certain number (a-b), b is undefined, it will equal the function at that point. If we know the value of the function at point a, and the derivative of the function at point a, then we can rewrite the equation in terms of b (the undefined point on the x-axis.) Rewritten as such:
b=a-f(a)/f'(a). Using this equation, we can use any point on the x-axis, find the next point, then use that one, and repeat until we hit one of the zeroes.

## Step 2: Let's Find the Roots!

Polynomials work really well for this. Finding their derivatives is easy, and if they're large polynomials, there's no other way to get an accurate estimate of the zeroes.
First things first, graph your function. You can use a spreadsheet program, your calculator, whatever works for you. Make a rough estimate as to where the zeroes are. Looking at the graph below, I'm going to estimate that there are zeroes at -2, -1.2, 1.2, and 3. Let's see how accurate my guess is.

## Step 3: Let Excel Do the Work

We're going to let excel take care of all the guesswork for us. In your spreadsheet you're going to need 3 columns. One for your x-values, one for your function, and one for its derivative.
In cell A1 put in your guess for one of the zeroes. In cell B2 put in your equation as a function of cell A1 (shown in the picture.) In cell C1 put in your derivative as a function of A1.
In cell A2 type in "=A1-B1/C1" this is the equation I showed you a couple steps earlier. Now drag all three cells down 10 or so spaces. The last cell in column A should be your root. To check your answer, make sure that the last cell in column A matches the next to last cell. If any of the numbers are different, drag all three cells down even further. Congrats, there's the first root.

## Step 4: Find the Other Roots

Now that you've found your first root, it's time to find the other ones. Put your next guess in cell A1, make sure your spreadsheet program has calculated your next root. Sometimes you need to hit a CALCULATE button or something. I attached a sample spreadsheet below.
That's how Newton's method works.

## Recommendations

• ### 3D CAM and CNC Class

582 Enrolled

• ### Oil Contest

We have a be nice policy.