Add-ins: Excel
PowerPoint
How the PLOT program does what it does
The program draws a graph for any function that can be entered as a formula in an Excel cell. This graph will always be in a new sheet that it adds to the active workbook. In addition to automating the steps required to create a graph in Excel, the program adds an important refinement. It concentrates its efforts on those parts of the graph with the greater curvature greater. The value of this feature is best illustrated by showing the results for a very important function, the LOG function. Plotting for values between x = 0 and x = 10, the results are:
|
|
|
The left-hand figure is from the PLOT software. Note that the density of points in the region from x = 0 to x = 1 is much greater than that in the region from x = 1 to x = 10. This is because the PLOT software concentrates its effort in the region where the graph is curved rather than where the graph 'straightens out.' Keep in mind that one needs only two points to draw a straight line, but a lot more effort is required to draw a curved shape. This dynamic selection of x values lets the software better define the shape of the LOG function for small values of x.
Extract the contents of the downloaded file into a directory of your choice. There are two files, the add-in, Plot.xla, and the help file, Plot.chm. Run Excel and load the Plot.xla as an add-in (Tools | Add-In...)
For more details see Common Installation Instructions.
The PLOT program is available from the TM menu
The dialog box lets you establish what function you want graphed and the parameters to control the process
|
|
This is the cell that contains the independent variable. In most instances it is known as the x variable and its values will be graphed on the x-axis of the resulting graph. For example, consider a graph y = Log (x), with the formula in cell $A$2 being =LOG(A1). Then, cell A1 refers to the independent variable.
This cell is the one that contains the result of the function being graphed. For example, consider a graph y = Log (x), with the formula in cell $A$2 being =LOG(A1). Then cell A2 refers to the dependent variable.
These two dialog box entries specify the domain over which the graph is to be drawn. For example, in the graph shown in the introduction the minimum value would be 0 and the maximum would be 10. Each of these dialog box entries can be either a number or a reference to a single cell, which would contain a number.
The PLOT software draws a crude graph using the number of points specified in the 'Minimum (to Start with)' dialog box entry. It refines its work and stops when it has at least as many data points as specified in the 'Maximum (to stop) entry. Without any information about the function, good values to try would be 5 and 100. See the advanced tips section for more information on how the judicious use of these two dialog box entries can significantly improve the graph.
Use the OK button to run the software with the specified information.
Use the Cancel button to close the dialog box without running the PLOT software.
Displays the help file (this file). If it does not work, it is because the version of Excel that you are using does not support the new Microsoft help system. Try double-clicking the plot.chm file from Windows. If that doesn't work, you can always get help from my web site at www.tushar-mehta.com.
The PLOT software always adds a new worksheet to the active workbook and uses this new sheet for its calculations and the final graph.
In creating the data for the graph, it starts by checking the function at some number of x values. How many values it uses to start is controlled by the dialog box entry 'minimum data points to start with.' It subsequently doubles the number of data points it uses. However, it does not allocate these new data points in an equidistant fashion as the first time around. Instead it uses the information about the curvature of the graph at each of earlier data points and uses more points in the areas where the curvature is greater.
The program keeps on doubling the data points it uses until it has more than the number specified in the dialog box entry 'Maximum (to stop with).'
It creates the graph as an embedded chart object in the new worksheet added to the workbook.
Tips on improving the graph of a difficult-to-graph function (such as a step function)
Comparing the graph of Tan(x) drawn with the Plot Manager and an Excel table
The graphs below are the PLOT Manager results for various functions. Click on a thumbnail to see the original graph.
|
|
 y=sin(x) |
 y=cos(x) |
 y=ex*sin(x2) |
 y=ex*sin(x2) |
 y=tan(x) |
 y=sin(x)/x |
 y=1/x |
 y=log(x) |
 Step function y=trunc(x) |
 Standard normal distribution,  |
As it happens a function with a lot of sharp twists is difficult for a software program to plot. The step function, y=Trunc(x) has lots of sharp turns. The results of plotting the function three different ways is shown below. Note that the Plot Manager does much better than a more straightforward way, such as with the use of an Excel Table. In addition, the Plot Manager is comparable to the result of Mathematica, a much more sophisticated and expensive program. One might even argue that Mathematica fails to do a complete job since it misses the the two points (-5, -5) and the (5, 5) points altogether!
 Step function, Trunc(x), with Plot Manager |
 Step function, Trunc(x), with Excel Table |
 Step function, Trunc(x), with Mathematica |
The function y = Tan(x) is another demonstration of how the Plot Manager software does a better job of graphing functions than more common methods. In this case, the function has two asymptotes, at x = -p/2 and at x = p/2 respectively. Note that the x values correspond to x = -90° and x = 90°. The results with the Plot Manager and with an Excel data table are shown below.
 Tan(x) with the Plot Manager |
 Tan(x) with an Excel table |
The graph on the left clearly identifies the asymptotes as shown by the vertical lines at x = -p/2 and at x = p/2. In the chart to the right, the function does not clearly show that it goes to +∞ or to -∞.
It is possible to improve the appearance of the graph which has asymptotes by reducing the range of the y-axis, as shown below.
 Tan(x) with the Plot Manager with a restricted y-axis |
 Tan(x) with an Excel table with a restricted y-axis |
In the two examples above, the y-axis is restricted to values between -20 and +20. Note that the result of the Excel table now looks much better. However, the asymptotic nature of the graph is still more easily noticed in the PLOT Manager output.
While people would have no problem visualizing a step function such as y=Trunc (x), most software programs have difficulty plotting it. This is because there are so many 'sharp edges' (jumps) in the function. .
In cases such as this, where we know something about the function's behavior, it helps the software if we give it additional guidance. Consider plotting the Trunc(x) function from x = -10 to x = 10 with the Plot Manager. Starting with 5 data points and ending with 100 yields the plot on the left below. Notice there are many instances where the software draws line at an angle rather than a vertical line.
|
|
|
Since we know about the discontinuities at each integer value, telling the software to start with 21 points and continuing until it has at least 200 yields the curve on the right. Notice that most, though not all, of the transitions that occur at integer values are are now drawn with vertical lines. Of course, since we changed the number of starting points and the number of ending points, can we tell which was responsible for the improvement?
To help answer that question, we look at the same function, Trunc(x), plotted from x = -5 to x = 5 (as shown below).
|
|
|
On the left above, the software started with 11 data points and finished with 100, while on the right, it started with 11 and finished with 200 data points. Notice that there is no perceptible difference between the two graphs. Consequently, we can conclude that it was information about the 11 distinct x-values that helped it draw a better graph. Remember that we knew, from our knowledge of the function, that it changes in an abrupt fashion at each of the 10 non-zero integer values between -5 and +5 .
