9.1 Numerical Differentiation

We will describe a procedure which you can use to compute and plot the derivative of any function you can enter into a spreadsheet, which includes all functions we have mentioned so far, and many others.

First though, we will digress to see what happens when we differentiate a given function at a given point. The derivative that we seek is the ratio of the change in the function value to the change in its argument near that given point. To find such changes you need to pick two arguments and compute this ratio.

Our plan will be to take two arguments that differ by a certain amount d, and calculate this ratio for these two arguments on one line of the spreadsheet, then reduce d and do it again on subsequent lines, We can then look at what happens to the estimate of the derivative that we find.

This plan is worked out in detail in Chapter 7 of 18.013A and you should go over that Chapter carefully, and implement it yourself on a spreadsheet, for whatever function you choose, say exp(-x2) at x = 1. You can then change arguments and functions easily.

The purpose of this activity is to familiarize yourself with how well taking a finite difference ratio of value change to argument change, which is what you will do, comes close to giving the derivative for typical functions, and conversely what you can expect to learn about finite changes in arguments from derivatives.

The following lesson can be learned from this activity.
If you are considering a function which has no large parameters in it, and use the "symmetric derivative approximation" with d = 10-4, you will get very close to the true derivative of your function, most of the time. The symmetric derivative approximation is

You can check the accuracy of this approximation by changing d to and observing whether this changes your answer materially. If not, your answer is probably accurate. If your answer changes very much, repeat with smaller d.

With my spreadsheet I can usually find derivatives numerically using this formula (or the extrapolations of it described in Chapter 7) that are accurate to ten decimal places. In practice, that kind of accuracy is hardly ever needed and we can stand answers that are accurate to two decimal places, most of the time.

How can I use this to check my answers for derivatives?

One way is to set up your answer for the derivative in terms of the given argument, and compare it to the derivative that the spreadsheet in Chapter 7 gives you for your function. You can then pick two or three arguments at random and see if the answers agree at each. If they do you are probably right.

Of course it is possible that your answer is right at the exact arguments you chose, but is wrong elsewhere. So you can plot your answer and the machines answer both and see if they agree entirely.

And how can I do that?

You can set up a spreadsheet that has the argument change from row to row rather than having d change. You can fix d at say 10^-4, and make a column (say A) in which x increases from some initial value (in say A5) by say q per row (put A6 =A5+C$1 for q in C1), and in the next column (B) put x + d (B5=A5+B$1 with d in B1) and in the next column (C) put x-d (set C5=A5-B$1).Copy A6, B5 and C5 down the columns as far as you want to. Then in the next column put f(x) (D5 = f(A5)) and copy this down and into columns E and F as well.

Now in column G you can copy column A (set G5=A5), and in H you can put H5 = (E5-D5)/2/B$1, and copy G5 and H5 down their columns. In I5 you can put your solution for f'(A5) and copy that down column I.

If you highlight and plot columns G H and I using an x y scatter chart you will get plots of the numerical derivative and of your derivative, which should overlap completely if you are right, You can change the contents of B1 to change d, or change the domain by changing the initial argument (in A5) or the value of q in C!

Once this is set up, you can change functions by just changing D5 and I5 appropriately to the new function and new proposed derivative of it, and copy the former into columns D E and F and the latter down column I.

Exercise 8.6 Make yourself a spreadsheet derivative checker of this kind and use it to check the derivative of x2 (sin (x)) exp(-x2). (First find this derivative from the rules)

I have a problem, When I graph a function that goes to infinity, like tan x, on a spreadsheet, my picture stinks. The function looks to be 0 except at spikes.

This happens because the program that does the charting chooses a scale for the graph based on the largest numbers that occur in the data. When these are gigantic, like a trillion, the top of the graph corresponds to the value of a trillion. Similar things happen for negative values at the bottom of the graph. This means that ordinary numbers, like 1 or 19 or 1000 all look like 0 on this scale. Its like what you look like to someone trying to pick you out while he or she is on Mars. So your graph will look like all 0's except where the function gets very big or very negative.

What can I do about it?

The easiest thing is to cut out the high values. One way to do this is only highlight rows with reasonable function values when you make your chart. Another way is to put in a cutoff in what you plot.

A cutoff? How can I do that?

Generally speaking you and I can make visual sense out of differences that are of the order of ten or so. Thus if you are dealing with a function most of whose values are around 1, if you want to look at its behavior at those values you might want to cut the function off at 10.

Sure, but how?

Suppose your argument column is column F and the values of your function are in column G. Then copy column F into column X , say, (set X1=F1 and copy this down column X) and in column Y1 put =Min(g1,10) and copy this down column Y ; If you highlight and chart columns X and Y your function will cut off. A better idea is to set w1=10 and set Y1=min(g1,w$1). This way you can change the cutoff value if you do not like it, by changing w1. To cut off at 10 and -10 you can put instead Y1=max(min(g1,10),-10).

Exercise 8.7: Try plotting the tangent function (=tan F1) with various cutoffs until you like the look of your plot.