| Excel Companion Chapter 1 section 5 |
In many problem-solving situations we need to obtain accurate tables and graphs of functions. Graphing is such a fundamental activity that we strongly encourage you to work through the step by step process of building a basic table and graph found in Appendix A. Once you have built a table and graph from scratch, you will have a better understanding and appreciation of the enhanced graphing utility Plot.xls. Open the workbook PLOT.xls and click the tab for table f g h.
This worksheet enables you to easily create tables for one, two, or three functions. Different graphical views are possible by adjusting your choice for xmin, the lower value of the independent variable x, and your choice for x incr, the size of the increment. By changing these values, you can perform the equivalent of zooming in or zooming out. The gray buttons correspond to individual macros, which are sequences of pre-recorded Excel commands. Pressing on one of these macro buttons updates the table and graph of the function by automatically performing steps that must be executed manually in Appendix A.
We illustrate plot f g h with a cubic
function: 
and a
quadratic function 
. The third function, h, will be
intentionally left blank. Start by clicking the mouse in the blue
cell B11 which contains a formula for the function f. In the
formula bar, enter the formula =x^3 - 20*x + 10 and press
the <Enter> key. Use the formula bar for making
corrections, changes, or deletions at any time. If Excel displays
the message "ERROR IN FORMULA" then press OK and make
your correction. The most common error is the omission of the
asterisk symbol * for multiplication.
Another common error is the omission of the = sign at the
beginning of the function definition. Once the formula is
correct, click the mouse on the button new_f and
wait a few seconds for the corresponding table and graph to be
updated. The scale on the y-axis automatically adjusts to fit the
data unless some other option has been specified. If the starting
value is 0 and the x increment is 1 then y values for the cubic
function, y = f(x), go beyond 100,000. The parabola, y = g(x),
will appear to be linear. To modify the scale, scroll up to the
top of the sheet table f g h, click in cell A4 and change
the starting value to -5. This change alone does not give us a
very good view of the graph near the origin. We need to enter a
smaller increment in order to zoom in. Click on cell B4 and
change the increment size to 1/4 = 0.25. As soon as we press the
<Enter> key the values of y are re-calculated and the graph
is re-drawn. Definitions for the other functions are entered in
the same way. Enter the function g(x) by typing the defining
formula =3*x^2-20, in cell C11 followed by <Enter>
and then pressing the new_g button. To zoom in
around the x value of 2, change the starting value to some number
just to the left of 2 and the increment to some smaller value. If
you wish to erase a function that has been previously defined,
simply delete its defining formula in row 11 and then press its
corresponding macro button.
The utility Plot.xls allows you to directly control the size of the increment between successive x values in the domain of the function. You have indirect control over the maximum value of x. If you choose xmin to be -2 and an increment of 0.25 then the x coordinate of the fiftieth point will be 10.25. You may prefer to have more direct control over the maximum value of x and let the increment be calculated as (xmax - xmin)/50. Graphical calculators are programmed to work with xmin and xmax instead of with xmin and a x-increment. We have stored an alternative to Plot.xls in a file called GrafCalc.xls which operates more like a graphics calculator.
A common error made by beginners is to attempt to enter a new defining formula by changing a label for a function found above the graph in sheet table f g h, rather than by changing the defining formula which is in the first numerical row of the table. Excel formulas always begin with an = sign. Manually changing a function label will have no effect on the tabular values or on the graph. In general, important formulas and parameters that are designed to be modified are located in cells with a blue background.
 |
This plot utility also contains a block of cells
which prepares the worksheet for the application of goal seeking
or for the Solver Add-in. Observe that the function crosses the x axis at
three places.
We illustrate goal seeking in finding the root near 4. To find a good approximation for this root, we enter the value 4 ( or any number close to 4) in cell C7. From the Tool menu we invoke goal seek and complete the dialog box as shown below. The target cell E7 (Set cell) must contain a formula. Our goal here is to have Excel drive the value of the target cell to zero by adjusting the numerical value in the changing cell, C7. Goal seeking gives us a root at x = 4.1973 accurate to four decimal places.
Appendix D describes two widely known algorithms for root finding, one known as Newton’s method, uses calculus. The other is known as the method of interval bisection.
QUESTIONS
Copyright © Joseph F. Aieta, Babson
College 1997
