Volume 7. January 2007. Article ID 1364
Students at every level benefit from seeing mathematical concepts illustrated with well-designed mathlets. In this article, I present a five-step procedure to help you construct your own mathlets in Excel. I demonstrate the use of the procedure with one application: properties of the tangent line to the curve of a differentiable function.
All students benefit from seeing mathematical concepts illustrated with well-designed mathlets. Although you can use other computer languages, I recommend the use of Excel, because it has a powerful platform for creating impressive mathlets, and because it is widely available to teachers and students. I will present an application of tangent lines to the graph of a function to illustrate how Excel mathlets are created and used in my classroom. You will need to be familiar with Excel to understand and apply the five step process described in this article.
I use an Excel mathlet in my calculus classes to explore many of the interesting properties of the tangent line. The mathlet shows the graph of a function y = f(x), a point (x0 , y0) on the graph of f, a line tangent to the graph at the point (x0 , y0), a vertical reference line (x = x0), and a horizontal reference line (y = y0). Above the graph is an information bar containing the values of x0, y0 = f(x0), f '(x0), and f ''(x0). An image of the mathlet is shown in Figure 1 below. You can click on the image to open or download the Excel file and explore the mathlet.
Figure 1. The Tangent Line mathlet
I can move the point (x0, y0) along the curve in either direction. As (x0, y0) moves along the curve, I show the students how the tangent line glides along the curve like a snowboard on a snowy hill. When the curve is increasing at x0, the tangent line has a positive slope and f '(x0) > 0. When the curve is decreasing at x0, the tangent line has a negative slope and f '(x0) < 0. The steeper the curve, the steeper is the tangent line. At the top of the hills and at the bottom of the valleys, I show them how the tangent line is horizontal and the slope is zero.
I use the mathlet to show my students how the ends of the snow board are above the hill when the curve is concave down and below the hill when the curve is concave up. I also have them see that when the curve is concave down, the value of the second derivative is negative and when the curve is concave up, the second derivative is positive. As I glide through the inflection point, they can see that one side of the snowboard is above the hill and the other side is below the hill and that at the inflection point itself, f ''(x0) = 0.
Finally, I give them a value of x0 and ask them to estimate the value of the function, the value of the first derivative, and the value of the second derivative. After giving them a chance to guess these values, I jump to the specified x0 so that they can judge the accuracy of their estimates. As you can see, this mathlet is a powerful teaching tool.
The mathlet is operated by three macro keys:
Explore the mathlet yourself. In the next section, we will see how it's constructed.
Here are the five steps for creating Excel mathlets:
As an example, we will go through these steps in the construction of the Tangent Line mathlet. However, you would use a similar process for other mathlets.
Components are objects, which appear on the screen. This mathlet has five components:
Components are then classified as
A component is vertical if any part of the component is perpendicular to the x-axis. All other components are non-vertical. Component (e), the vertical reference line, is the only vertical component. A component is stationary, if it never moves during the operation of the mathlet. Of the five components, only the graph of f(x) is stationary.
Add the components and their classifications to the Objects grid template as shown in Figure 2. In Figure 3 we show the template after adding the components and classifications.
Figure 2. Objects grid template
Figure 3. Completed objects grid template
The Objects grid requires one column for the x-values and one column for each component. This mathlet thus requires six columns. Note also that the Object grid requires blocks of rows, which will be called row-blocks in this article. Grids require one row-block to take care of all non-vertical components, and one row-block for each vertical component. This mathlet requires two row-blocks. Row-block 1 consists of 401 rows for the four non-vertical components. Row-block 2 consists of two rows for the vertical reference line.
Let the function f be defined by
Then it follows that:
and
The equation of the tangent line to the curve of f at x = x0 is given by:
y = f(x0) + f '(x0)(x − x0) = y0 + f '(x0)(x − x0)
The function f is defined on the domain
[0, 8].
Fill in the numbers from 0 to 8 in increments of 0.02 in the 401 rows (B9:B409
) of row-block 1. These are the x-values. See Figure 4.
Figure 4. Objects grid with x-values entered
How do moving objects move? Let
(x0, y0)
be the point of tangency. Insert the definition of
f(x) in cell C9:
= -.25*B9^3 + 3*B9^2 - 9*B9 + 5
and then copy cell C9 to cells C10 to C409.
Define a function g as follows:
Let cell D2 always contain the current value of x0. By inserting the definition of g(x) in each of the cells D9 to D409, all but one these cells will have the value −50. The one exception is the cell corresponding to x = x0. Since g(x) = −50 will be off (below) the chart, the value g(x0) is the only value that can be seen. As the value in cell D2 increases from 0 to 8 in increments of 0.02, this point of tangency glides along the curve. Because of computer round-off error, modify the function of g as follows:
Thinking of the formula in D9 in the following way:
helps with its translation to an Excel formula:
= IF(AND(B9 >= $D$2 - 0.01, B9 <= $D$2 + 0.01), -.25 * B9^3 + 3 * B9^2 - 9 * B9 + 5, -50)
Note that the IF
statement in Excel has the following syntax:
= IF(logicalText, valueIfTrue, valueIfFalse)
Let's define critical cells as cells which contain critical information about the location of a component. This mathlet requires six critical cells:
What about the tangent line? The line will be displayed on the interval (x0 − 0.5, x0 + 0.5). So define a function t defined on the interval [0, 8] as follows:
Recall that the values of x0, f(x0), f '(x0) are contained in cells D2, D3, and D4 respectively and that the x-coordinates of the endpoints of the tangent line are contained in cells E2 and E3 respectively. Cell E9 is defined as:
The Excel formula for cell E9 is therefore written as:
= IF(AND(B9 >= $E$2, B9 <= $E$3), $D$3 + $D$4 * (B9 - $D$2), -50)
The horizontal reference line
y = y0
extends from the y-axis to the point
(x0, y0).
The formulas for this component will be constructed in column F, so cell F9 should read = IF(B9 <= $D$2, $D$3, -50)
. The last component is the vertical reference line. It extends between the points
(x0, 0)
and
(x0, y0).
Since this is the only vertical component, its formulas are set up in row-block 2. Cells B410 and B411 contain the x-coordinates for these two points and should both read = $D$2
. Cells G410 and G411 contain the y-coordinates for these two points and should read 0 and = D3
respectively.
Figure 5 summarizes these crucial formulas. The formulas are defined in row 9 of columns C, D, E, and F and then copied down to the remaining cells in row-block 1.
Figure 5. The critical formulas for the five components and for the critical cells
The Critical Cell panel is a block of four rows at the top of the spreadsheet which contains the critical cells. See Figure 5 again for formulas.
Chart wizard is an Excel template that guides the user through the steps for creating graphs. Highlight the entire Object grid (B8:G411), including the labels at the top of the columns, and then either click on the Chart Wizard icon or choose the Chart option under the Insert pull-down menu. Choose the following options in the four steps of the chart wizard (see Figure 6).
After finishing the Chart wizard, the graph should look like Figure 7.
Figure 7. The graph after finishing Chart Wizard
Edit the graph. First, change point sizes, point symbols, line types, etc. For these five components, choose the settings shown in the Object Table. Make these changes by double-clicking on the desired object and then selecting the preferred options in the Patterns tab of the Format Data Series window. Recall that the formula for three objects--the point of tangency, the tangent line and the horizontal reference line--all had the form:
When formatting the series for these objects, it is important that Line be suppressed by choosing the option None under Line (see the Object Table). Otherwise, the graph will have a line dipping down to −50
Change the background color to white by double-clicking on the background and choosing the following options: Under Area, select None. Select Chart Options under the Chart pull-down menu to remove grid lines and the legend.
Finally set the y-axis scale to Minimum: . −4, Maximum: 8, and Major Unit: 2 in the Format Axis window, which is accessed by double-clicking on any numerical value on the y-axis and choosing the Scale tab.
The mathlet also has a Parameter Information Bar, a strip of cells (J1:S1) above the graph giving numerical information (x0, y0 = f(x0), f '(x0), and f ''(x0)), about the graph itself. Formulas for the Parameter Information Bar are given in Figure 8.
Figure 8. The information bar
Macros are used to manipulate the values in the critical cells. Each macro is assigned a shortcut key, which is used during the operation of the mathlet. This mathlet has three macros:
You may view or download the Visual Basic code for these macros. In general, creating macros is a two-step process. The first step is to name the macro and write its Visual Basic code. Begin by simultaneously pressing the alt key and the F8 key (alt+F8). Continue by typing the name of the macro in the Macro Name box, and then left-clicking on the Create button. Type in the code and finally close the Visual basic window. The second step is to assign a short-cut key to the macro. Type alt+F8 and highlight the desired macro. Then select the Options button and insert a letter in the Shortcut Key box.
Try the Tangent Line mathlet yourself, modify it, and then write your own mathlets. It requires just five easy steps. The following resources may also be helpful: