Investigating differentiation from first principles using Excel

Suppose we have a point on the graph of a function . The point on the graph a horizontal distance from would be . We can use the line segmentbetween these two points as the hypotenuse of a right-angled triangle as shown in the figure below. Pay particular attention to the width and height of this triangle.

1.What is the gradient of the hypotenuse?

The derivative of a function at a point is defined as the limit of the slope of the secant through as the width of this triangle goes to zero. That is

This equation is just the limit of the rise over the run, so really it is nothing new except now you are taking the run to be smaller and smaller(the limit as h approaches zero) to get a better and better idea of the slope of the tangent line or instantaneous rate of change at the point .

We can use Excel to investigate the limit of this quotient as h approaches zero.

Open the Excel file ‘First Principles’.

Column A has positive integers in it which we use to create a sequence of numbers h which converge to zero. We have an infinite number of sequences which we could use as h, so I have chosen two sequences, one which converges relatively fast and the other that convergences relatively slowly. The point at which we are trying to differentiate is the orange cell D4. The blue cell E5 is where you need to type the slope of the secant expression depending on your function and your choice of fast or slow convergence.

A quadratic function

Let’s examine the typical example. This function squares its input for its output. In the cell E5 type =((D$4+B5)^2-(D$4)^2)/B5, and hit enter. Does this expression make sense to you?

Step 1:Click on E5, scroll down to E10004and while holding shift, click on E10004.
Step 2:Press Ctrl+D to ‘fill down’

  1. What do you suspect the slope of the secant is converging to?

Now in the formula for E5 change the references of B5 to C5 to use a slower converging h. This will only change the one cell, use steps 1-2 above to change all the blue cells.

  1. Does this sequence in column Econverge to the same number as before? Does it drop to zero like before?Does the sequence display any other interesting behaviour that it didn’t before?
  2. Change the value of a in the orange cell to the following values and note what the sequence converges to: 0, 0.5, 2, 3, 4, 5.Recall you are finding the derivative of f(x) at these 6 points.

A cubic function

Let’s move to another function. Input the slope of the secant quotient for the function

and observe the limit of the slope of the secant.

  1. Again change the value of a in the orange cell to the following values and note the limit of the sequence: 0, 0.5, 1, 2, 3, 4, 5.

An exponential and trigonometric function

The exponential function is built into Excel. Let’s use this to investigate its derivative at the point a. Type into E5=(EXP(D$4+B5)-EXP(D$4))/B5.

  1. Is the above formula using the spreadsheet’s fast convergence or slow convergence sequence? Do you understand where this formula has come from?
  2. Compare the derivative of the functionat to the valueof, for various values of a.

The sine function is built into Excel too, it is typed as SIN(), where the input for sine is in the brackets and assumed to be in radians not degrees.

  1. Find the derivative of at and compare this with the derivative of at .

The derivative of sine using Excel

It is well known to students of calculus that the derivative of sine is cosine, but most do not realise that sine and cosine must be in radians and not degrees. Let’s observe that when in radians .

Open the Excel file ‘Differentiating sine’.

To find the derivative we need to look at for small h, or in another notation for small. Instead of looking at the limit as h or goes to zero at one particular point a, in this activity we will just choose a small and examine many points along the curve .

In the cell B5 type =SIN(A5).

Step 1: Click on B5, scroll down to G10004 and while holding shift, click on G10004. |
Step 2: Press Ctrl+D to 'fill down'

In the cell D5 type =COS(A5) and following the steps 1 to 2 above for column D. Now we want to write a formula in C5 that gives the change in over the change in . Write the formula which achieves this in the cell C5, and check with your teacher once you have done this. Follow steps 1 to 2 above for the column C.

  1. Do you notice a similarity between two columns? Does this similarity continue further down the spread sheet? What do you think this means?
  2. Why aren’t these two columns identical?

Let’s use Excel to plot a graph of our data so we can graphically compare cosine to our approximation of the derivate of sine. Select cells A3 down to D10004.

  1. Why do you think we have avoided using the last row D10005?

Now with these cells selected (except the last row) click on the ‘Insert’ tab, then in the ‘Charts’ section click on ‘Scatter’ and choose any of these scatter plots.

  1. What are the units of the horizontal axis?
  2. Does this graph agree with your thoughts in question 1?

Investigate what this plot looks like if you use a column, line, bar or different type of scatter plot.

Teacher Resource Investigating differentiation from first principles using Excel

  1. .
  2. Converges to 2
  3. This sequence still converges to 2, but a lot slower and it doesn’t drop to zero like it did before. Notice eventually the computed slope of the secant oscillates around 2, in that it no longer stays at or above 2 but dips below, see the cell for an example E9782.This only occurs because of computational imprecision in Excel.
  4. The limits are 0, 1, 4, 6, 8 and 10 for a=0, 0.5, 2, 3, 4, 5.
  5. =((D$4+C5)^3+D$4+C5-1-(D$4)^3-D$4+1)/C5 should be your input in E5. The limits are 1, 1.75, 4, 13, 28, 49 and 76 for a=0, 0.5, 1, 2, 3, 4, 5.
  6. Uses the cells in column B and they converge quickly. This equation is simply the slope of the secant where the function is the exponential .
  7. The derivative should equal the value of for each a.
  8. From the spreadsheet is seems that the derivative of at is around 0.540302 which closely agrees with the true limit of . The derivative of

at the same point is clearly 1 and there should be no reason to believe they would be the same.

Teacher Resource The derivative of sine using Excel

  1. The expression in C5 should be =(sin(A6)-sin(A5))/(A6-A5). Column C and D are quite similar, not identical, but close. This continues further down the spreadsheet. This means that the slope of the secant at a point xfor small is very close to the value of , or that the derivative of the appears to be .
  2. The value of although small (0.001) is still not as exact as finding the limit as approaches 0.
  3. Notice in the last row the value for the gradient is not at all similar to the value of , this is because the formula for the gradient in cell C10005 relies on the value in the cell B10006, and there is no value in this cell because the row 10005 is our last.
  4. Radians.
  5. It should, if you can’t see one of the lines, this is because it lies behind another.