APPENDIX A

Graphing a Function

This appendix takes you through the steps of building a basic function graphing utility. Unlike the more elaborate utility Plot.xls, this simpler worksheet does not include macros or any fancy formatting.

Start with an empty Excel workbook, by selecting File... New… from the main menu.

Note: The major objectives for each step are underlined. What you actually type as a label, value, or formula appears in boldface. Menu Choices are in italics. You must remember to press the <Enter> key after entering each label, value, or formula.

1. Place a title in the first row and center it in columns A through G.

Click in cell A1 and type Graphing a Function

Click in cell A1 and drag the mouse to the right until cells A1 through G1 are shown with a black background. Release the mouse button.

From the main menu, select Format...Cells... then click the Alignment tab, select Center Across Selection and then click OK. [As a shortcut you could use the icon ¬ a® on the Formatting toolbar]

2. Enter identifying labels for the lowest value of x, the increment, the column for the independent variable x , the column for the dependent variable y , and the total number of points.

Click in cell A2 and type xmin This will be the left endpoint of the domain of your function

Click in cell A3 and type xincr. This value is added to each previous value of x in the x column.

Click in cell A5 and type number of points is

Click in cell C5 and type 20

Click in cell A10 and type x

Click in cell B10 and type y = f(x)

Highlight cells A10 and B10 and press U, the underline icon, in the Formatting toolbar.

While these cells are highlighted, press the centering icon on the Formatting toolbar.

3. Enter initial values for xmin and xincr and associate these cells with the names to their left

Click in cell B2 and type -2

Click in cell B3 and type .5

Highlight the block of cells A2:B3 and then Insert...Name...Create...Left Column

4. Create the series of x values by making xmin the starting value. Add the increment, xincr, to successive rows until you have twenty points.

Click in cell A11 and type =xmin. The number -2 should appear in A11. Click in cell A12 and enter the formula =A11 + xincr. The number -1.5 should appear in cell A12. To copy this formula down through cell A30, select cell A12 and click on the cell handle (the small black square in the lower right). The pointer changes to a crossbar. Hold down the left mouse button as you drag all the way down from cell A12 to cell A30 and let up on the mouse button. Observe that the first term in each of the copied formulas from row 13 down to row 30 refers to the previous cell in column A (relative reference) but the other term of the formula always refers to the same cell named xincr, which is cell B3 (this is an example of an absolute or fixed reference). Obviously you could have chosen fewer or more points at this stage. It is relatively easy to change the number of points in our table, ass we will see later.

5. Name the range of cells containing values of the independent variable x.

Highlight the range of cells A10:A30 containing the heading ‘x’ and all of the x values. From the menu bar, select Insert...Name...Create...Top Row to give this range the name which is immediately above the data series in column A. Naming this data series x will enable us to use the variable x in the defining formula for f(x).

6. Enter the defining formula for f(x) in the cell adjacent to the first x value. Copy this formula down for all twenty points.

Click in B11 and type =.5* x^2-3* x+6. The number 14 should appear in B11.

Highlight B11, click on the cell handle in the lower right and drag it down to B30.

7. Identify the data series for your chart and invoke the Chart Wizard.

Highlight the range A10:B30 containing both the x and y coordinates of all 20 points.

Click the Chart Wizard icon on the standard toolbar. The pointer arrow should now contain a small crossbar with a tiny chart icon below it.

8. Choose the initial location and size of the chart

Click the mouse to anchor the upper left corner of the rectangle somewhere in cell C6 and then drag the mouse down and to the right. Once you have outlined the desired area, release the left mouse button. Don’t be too concerned about the initial size and location of this rectangular region since it can be easily adjusted later.

9. Display a chart of the points as a scatterplot with the points connected.

The Chart Wizard will present a series of five dialog boxes:

Dialog box 1:	The range addresses should be the cells that contain the x and y coordinates. If the range shown is not A10:B30 then hit Cancel and highlight the correct range or edit the range box.. Once the range is correctly defined, click the Next button.
Dialog box 2	: Click XY scatter as the chart type and click Next.
Dialog box 3:	Click format 2 to connect the points in the graph and then click Next.
Dialog box 4:	The Chart Wizard shows a preview of what your chart will look like under the selected option. Make sure that the Columns button is selected and that the Use First Column(s) for x data contains a 1 and then click Next.
Dialog box 5:	Click No under Add a Legend since there is only one y variable.Click the Chart Title box and type y as a function of x. Enter x and y as Axes Titles and then click Finish.

A quick way to change the size of a chart is to single click on or near the chart border and drag one of the corner handles. Dragging the other handles on the top, bottom, left or right borders will change one of the chart’s two dimensions. To move the chart, click on or near a border and drag the entire chart to a new location.

10. Display the defining formula as a label

Enter the label f(x) = in cell D3. .Select cell B11 and press the Copy icon. Click in cell E3 and press the Paste icon. An error message appears since there is no value of x in cell D3. Edit cell E3 by deleting the equal sign = and inserting a single quote ‘.

11. Save the file on your own data diskette in the A: drive

Select File … Save As… from the main menu. Choose the A: drive and name the file fctnplot.xls.

Suppose that you wanted to modify fctnplot.xls so that more points are plotted on the parabola. This can be done in several ways. One approach is to insert new rows in the table. A second approach is to add points at the end of the table. We will first describe how to add ten points to the graph by inserting ten new rows below the first point but above the last point of the original table.

Start with the worksheet in fctnplot.xls that contains the graph of the parabola

f(x) =.5* x^2-3* x+6 over the interval -2 < x < 7.5. and perform the following steps:

Highlight the rows numbered 16 through 25 by clicking and dragging on the row numbers.
Insert … Rows from the main menu and don’t be concerned with changes to the size of the graph.
Highlight the range A15:B:15, click on the cell handle in the lower right, and copy down to B40.

Your graph now has ten more points. Re-size or re-locate the graph as desired.

To illustrate the second approach we will add twenty new points by adding twenty new rows at the end of the original table. Again, you must start with the original worksheet in fctnplot.xls that contains the graph of the parabola f(x) =.5* x^2-3* x+6 over the interval -2 < x < 7.5. If necessary, close the sheet with thirty rows without saving it and open the original fctnplot.xls

In step 4 we copied the formula for the x values from A12 down to cell A30. Highlight cell A30, click on its fill handle, and copy the formula down through A50. Since the first x value is in row 11, the number of points is always ten less than the row number of the last point.

In step 5 we named the block of cells containing the x values. Redefine the x values by highlighting A10:A50 and selecting Insert...Name...Create...Top Row.

In step 6, we defined the y values as a function of the corresponding x values. Highlight B30, click on the cell
handle in the lower right, and copy down to B50.

Now we need to add these new points to the original chart of 20 points.

Highlight the block of (x,y) coordinates in the range A31:B50. Move the mouse so that the white cell pointer is pointing at this block of cells. Then drag and drop these new points into the chart.

Your graph now has twenty more points.. The worksheet should now show the graph of the parabola over the interval
-2 < x < 17.5. The scale on the y axis is automatically adjusted. You can resize or relocate the graph as desired.

Building worksheets does take time and effort. One major benefit to you is that you maintain a greater sense of control and ownership of the process. This feeling of empowerment is critical, especially when you are confronted with new situations. In most realistic applications of modeling you will need to build new model and will discover that no pre-existing worksheet is a suitable starting place.

At this point, enter different values into cells B2, B3, and B11 and see how these changes are reflected in the graph.. Note: whenever you change the function definition in B11 you must repeat step 6 and step 10. Some examples of different functions and x values for you to try are given below

Graph the function f(x) = (1.09)^xstarting at x = -4 with an increment of 1.

Graph the function f(x) = 1+.09*x starting at x = 0 with an increment of 0.5

Graph the function f(x) = 1/x starting at x = 0.1 with an increment of 0.1

You may want to modify individual features or elements of an Excel chart such as adding color to cell background and fonts. You may wish to change the characteristics of a chart such as colors, its type, and whether not the points of a scatterplot are connected by a smooth curve. Once a chart is activated by double-clicking on or within its border and certain elements have been selected (such as chart type, color, titles, axis labels, scales on each axis, etc.) commands can then be applied either from the menu bar or by clicking the right mouse button. The best way to learn is by experimenting.

Return to Table of Contents