Journal of Online Mathematics and Its Applications

Volume 7. January 2007. Article ID 1364

Write Your Own Excel Mathlets

Christoph Maier

Department of Mathematics

Indiana University of Pennsylvania


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.


Main Topics

  1. Introduction
  2. Five Easy Steps
  3. Resources

Ancillary Materials

1. Introduction

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.

The Tangent Line mathlet

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
Image: Mathlet.png

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:

  1. Press Ctrl+shift+r to move x0 to the right.
  2. Press Ctrl+shift+l to move x0 to the left.
  3. Press Ctrl+g to open a dialog box and set x0 to a desired value.

Explore the mathlet yourself. In the next section, we will see how it's constructed.

2. Five Easy Steps

Here are the five steps for creating Excel mathlets:

  1. Construct the Objects grid.
  2. Construct the Critical Cell panel.
  3. Use the magic of Chart Wizard.
  4. Construct the Parameter Information bar.
  5. Generate the macros.

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.

Step 1. Construct the objects grid

A. Determine and Classify All Components

Components are objects, which appear on the screen. This mathlet has five components:

  1. the graph of f(x);
  2. the point (x0, y0) of tangency on the graph of f(x);
  3. the tangent line to the graph of f(x) at (x0, y0) i.e. the line given by the equation: y = y0 + f '(x0) (xx0);
  4. the horizontal reference line, given by the equation y = y0;
  5. the vertical reference line, given by the equation x = x0.

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.

B. Construct the Objects grid

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
Image: ObjectGrid.png

Figure 3. Completed objects grid template
Image: ObjectGrid.png

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

Image: Equation1.gif

Then it follows that:

Image: Equation2.gif


Image: Equation3.gif

The equation of the tangent line to the curve of f at x = x0 is given by:

y = f(x0) + f '(x0)(xx0) = y0 + f '(x0)(xx0)

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
Image: ObjectGrid3.png

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:

Image: Equation4.gif

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:

Image: Equation5.gif

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:

  1. cell D2 contains the value of x0.
  2. cell D3 contains the value of f(x0)
  3. cell D4 contains the value of f '(x0)
  4. cell D5 contains the value of f ''(x0)
  5. cell E2 contains the x-coordinate corresponding to the lower end of the tangent line (i.e. x0 − 0.5).
  6. cell E3 contains the x-coordinate corresponding to the upper end of the tangent line (i.e. x0 + 0.5).

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:

Image: Equation6.gif

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:

Image: Equation7.gif

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

Step 2 construct the critical cell panel

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.

Step 3. Use the magic of chart wizard

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).

  1. Chart type = XY (Scatter), Chart sub-type = "Scatter with data points connected by lines"
  2. Data Range = $B$8:$G$411, Series In = "Columns"
  3. Chart Title = (Leave blank), Value(X) axis = x, Value(Y) axis= f(x)
  4. As Object in = Sheet1
Figure 6. The four steps in the Chart Wizard
Image: ChartWizard1.png Image: ChartWizard2.png
Image: ChartWizard3.png Image: ChartWizard4.png

After finishing the Chart wizard, the graph should look like Figure 7.

Figure 7. The graph after finishing Chart Wizard
Image: ChartWizardEnd.png

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:

Image: Equation8.gif

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.

Step 4. Construct the parameter information bar

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
Image: ImformationBar.png

Step 5. Macros

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:

  1. Macro move_right moves x0 in increments of 0.02 to the right.
  2. Macro move_left moves x0 in increments of 0.02 to the left.
  3. Macro choose_x_value allows the user to change x0 to a desired x-value.

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.

3. Resources

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:

  1. VBA: Visual Basic for Applications & Excel, by Gilberto E. Urroz. A good introduction writing macros in VBA, with examples and links to other sources.
  2. Introduction to VBA in Excel, by Robert L McDonald. A good Introduction to VBA.
  3. Spreadsheets With Something Extra (How to add explanatory messages and input boxes to your cells), by Lois S. Mahoney And Judith K. Welch. A good introduction to message boxes with examples.
  4. Super-Easy Guide to the Microsoft Office Excel 2003 Object Model, Microsoft Office Excel 2003 Technical Articles. A good, basic VBA Tutorial.