Creating A Straight-Line Display

The steps for building a dynamic graphical display may be split into three chronological phases—(1) Layout, (2) formula development, and (3) Graphing. The demonstration follows this order of the three phases. It is highly recommended to periodically save your work as you proceed through the design steps.

Both written step-by-step and animated versions of the instructions are available. As written technical instructions can sometimes be cumbersome, viewing the animations while reading the instructions (animations open in separate window) can reduce the effort in acquiring the expertise. The animations may be paused, resumed, stepped, and restarted as needed.

Layout

The design undertakes to build a graph of a straight line that reacts to coefficient changes in the function symbolic representation. The layout must provide for display of the function symbolically, data values derived from the function defined used for plotting, and a place for the graph. The layout chosen can be seen in Figure 1. A display of the function defined appears at the top left. To the right of the function, cells are used to define the x and y coordinates of the two points used to graph the line. The space below all of these cells contains the graph of the values in cells K6:L7. Click here to start an animation of the steps below.

Step 1: Type in the function symbolic display in the cells shown in Figure 1. The function of the straight-line is y = mx + b. Use a cell for each symbol of the function and a separate cell for each coefficient value all in the same row. Since the cells C6 and F6 will contain the coefficient values, label those cells using the cells located directly above them.

Step 2: Resize the width of the columns that contain the cells for displaying the function (columns A-F) to give the equation a typed looked and provide for display of the coefficient values. Tailor the look to suit your needs and preferences using color, cell alignment and bolding features. Note to select multiple cells click on cells while holding the Ctrl key down.

Step 3: Create the outline of the table that will contain the x and y coordinate values of the two data values used for creating the graph. At this time place the labels for this table in cells J5:J7, and K5 and L5. Place borders around the table cells if you prefer, although that will have no effect on the functioning of the tool being created.

This completes the layout phase of the design.

Formula Development

The purpose of these steps is to create the data used for the graph. As this is a linear function only two points need be created. On the right of Figure 1 each row of the table represents a point containing its x and y coordinate-values. If preferred use columns to represent the points, the only consequence comes when specifying the layout of the data in creating the graph during the next phase. The steps below follow the layout in Figure 1 where the data points are in rows. To start an animation demonstrating the steps click here.

Step 1: Place in cells K6 and K7 the independent x coordinate values. Cell K6 contains the smaller x-value and cell K7 contains the larger x-value. Whether the order is large to small or small to large is of no consequence. What is important is to choose independent x-values so as to have the graph of the line stretch across the entire chart x-axis.

Step 2: This step starts creation of the y coordinate values. Note that the formulas use both absolute (use of $ operator) and relative cell references. These are explained later in this step. Cell L6 calculates the y-value from the x-value in cell K6, while cell L7 calculates the y-value from the x-value in K7. This step creates the y-value formula in cell L6. Then in the next step the formula will be copied the to cell L7. Figure 2 shows the two y-value formulas already created in their respective cells and is for illustrative purposes. Creating and accepting these formulas causes the numerical result of the formula to show in the respective cell and not the formula. Now either type or use “Point Mode” to input into cell L6 the formula for a straight line to calculate the first point’s y-value. With “Point Mode” clicking on referenced cells and typing operators (+, - *, /, =) creates cell formulas. To create an absolute reference in point mode just press the F4 key immediately after clicking a referenced cell. The animation of these steps uses Point Mode to create the formula. The formula created for the first point makes reference to the x-value in cell K6, and the function coefficient values in cells C6 and F6. Be sure to make absolute reference to the cells containing the function coefficients (i.e. $C$6 and $F$6). The dollar signs instruct the copy operation used in the next step to replicate the formula in a manner that always references the cells containing the coefficients no matter where the formula is copied. The reference to the x-value cell (K6) is a relative reference (no dollars signs). This causes the second point y-value formula, created by copying the formula, to reference the second point x-value. In general, for any graphical tool developed, use the following rule for constructing the formulas used to calculate the dependent variable values. Employ absolute references to cells containing the function coefficient values and relative references to the cells containing the independent variable values.

Step 3: Copy the formula from cell L6 to the cell L7.

Step 4: Check out the correctness of the resulting y-values for various coefficient values.

Confirming the accuracy of the formulas completes creating the data needed to construct the graph.

Graphing

This is the phase where seldom-used point-and-click charting features are employed to create a dynamic graph of the function capable of responding to coefficient changes. The keys to this are choosing a chart type that allows for manual manipulation of both the y-axis and the x-axis, and using fixed axis scaling (the default on charts is automatic scaling). First use the Chart Wizard to create a chart and then customize the chart using formatting features. Click here to follow the animation while reading through the next steps.

Step 1: Select the cells containing the x and y coordinates and click the Chart Wizard button on the standard toolbar.

Step 2: From Step 1 of the chart wizard dialog box choose XY (Scatter) chart type and the “Scatter with data points connected by line” chart sub-type. The Scatter chart type is the only chart that provides complete manual control of both x and y-axes. The chart sub-type provides for the drawing of the line.

Step 3: In Step 2 of the chart wizard be sure that the “Series in columns” radio button is selected and accept the other settings. This tells the chart wizard that all the data point x-values are in one column and all the y-values are in the other column. If during the layout phase the data point x-value and y-value pairs were put in columns in the table, then the series (x-values and y-values) will be in rows.

Step 4: The third step of the chart wizard has several tabs. On the Titles tab place “Graph of a Straight Line y = mx + b” as the chart title, “x-value” in the Value (X) axis box, and “y-value” in the Value (Y) axis box. Alternatively, you may use axis labels of your choice. On the Legend tab uncheck the “Show legend” check box to hide the chart legend. On the Gridlines tab be sure only the Major gridlines check box on both the Value (X) axis and Value (Y) axis is checked. Do not change the settings on the Axes and Data Labels tabs.

Step 5: Step 4 of the chart wizard presents the choice of creating the chart on the current worksheet or on a separate worksheet. Be sure the “As object in” radio button is selected. Click the finish button to let the Chart Wizard create the chart of the straight line on the worksheet.

Figure 3 shows the chart on the spreadsheet as created by the Chart Wizard. Now employ formatting features to customize the chart. Features to scale the axes will be used to make the graph more adaptable to a range of function coefficient values. Other formatting features will make the graph more readable by coloring and emphasizing certain parts of the graph, and by resizing and repositioning the graph on the spreadsheet.

First format the x and y axes so that the plot of the line will stretch across the entire graph and the axes will be easier to read. This requires setting formatting features for each axis. Again follow the animation while reading the steps by clicking this link.

Step 1: To gain access to the axis formatting features for the x-axis, right click on the x-axis line and select Format Axis. It is important that the tip of the cursor arrow be placed directly on the line when you right click.

Step 2: There are several tabs on the format axis dialog box that appears. Select the Scale tab to turn off automatic scaling and manually set the axis scale. On the Scale tab be sure that all of the Auto checkboxes are unchecked. Then set the Minimum value to –10, the Maximum value to 10, and the Major unit value to 1. The setting of the Minor unit value does not matter as long as it does not exceed the Major unit value since we are displaying only major unit grid lines. Finally, set the “Value (Y) axis Crosses at” to 0. All other check boxes should be unchecked.

Step 3: Now select the Patterns tab. On this tab select the Custom Lines radio button. Select the red line color and the heaviest line weight from the drop down boxes. All other setting should be the default values.

Step 4: Then select the Font tab and make the following setting. Set Font to Arial, Font style to Regular, Size to 10, and Color to red. Leave all other settings set to their default values. The settings on the Patterns and Font tabs will make the x-axis easier to read. Click the OK button to accept the x-axis settings. This finishes formatting the x-axis.

Step 5: Now right click on the y-axis and select Format Axis making sure the tip of the arrow is on the y-axis. This opens the format axis dialog box for the y-axis. Set the options to the exact same settings used for the x-axis on the Scale, Patterns, and Font tabs. Then click the OK button to accept the y-axis settings.

Now the graph will be made more legible by emphasizing the plot of the graph line, resizing the graph, and repositioning the chart. Follow along with the animation by clicking here.

Step 1: Format the line to have a heavier weight. To access the line format features, right click on the plot of the line (make sure the tip of the arrow is on the line) and choose Format Data Series. When the Format Data Series dialog box appears, click on the Patterns tab. Set Line Weight to the heaviest option. As this is the only option that needs to be set, click the OK button to accept the change.

Step 2: Now move the chart so it is located directly below the function definition and data table. First, if the chart is not already selected, select the chart by clicking on it. Handlebars appear around the chart when it is selected. Drag the selected chart to a position directly under the function definition and data table already created.

Step 3: Then vertically resize the graph by dragging down the handlebar on the middle bottom of the chart until the gridlines form approximate squares. When the curser is on a handlebar it turns into a double-headed arrow. Figure 4 shows the chart after moving it and resizing it vertically.

The dynamic graph is now complete. To safeguard the tool from accidental changes protect static sections of the spreadsheet. The only cells that users should be able to change are the function coefficients (C6 and F6) and the x-values (K6 and K7). All other cells need to be protected from user changes. This protection is accomplished in two stages. First, unlock the changeable cells and then set the protection feature for the whole worksheet. Click here to follow the animations of the steps below.

Step 1: By default all cells are locked when a new worksheet is created. To unlock the indicated cells, first select the cells C6, F6, K6, and K7. To select all of these cells first click on one of the cells, and then hold down the Ctrl key while clicking each of the other cells. Then right click on one of the selected cells and choose Format Cells from the list.

Step 2: From the Format Cells dialog box choose the Protection tab, uncheck the Locked check box, and click OK.

Step 3: To protect the whole worksheet choose the Protect Sheet option from the Tools menu. When the Protect Sheet dialog box opens just accept the default options by clicking OK. If you don’t want your users to be able to change the protection feature of your worksheet, then enter a password in the box shown before clicking OK. Be sure to remember your password since forgotten passwords are irretrievable.

The tool is now protected from accidental changes to the chart, function definition form, and data creation sections. The user will only be able to change the coefficients in the function definition and the x-values used for plotting the line. One final optional touch is to turn off the worksheet cell gridlines. An animation of the steps below is available by clicking here.

Step 1: Choose Options from the Tools menu to open the Options dialog box.

Step 2: On the View tab of the Options dialog box uncheck the Gridlines check box under Window options and click OK.

Figure 5 shows the spreadsheet with the gridlines hidden. The Straight Line Dynamic Computer-Based tool is now complete. BE SURE TO SAVE THE FILE!

Accommodating Non-Linear Displays

Learning the methods for creating the linear tool just discussed is just the beginning. The methods for creating non-linear displays are the same as for the linear case except that non-linear displays require more than two points for plotting. Layout of the data points in non-linear tools must reflect this need. Figure 6 shows an active display for an Exponential function. The data values were moved to an area off the screen to reduce the complexity of the look. Figure 7 shows the location of the data values and Figure 8 displays the spreadsheet formulas in the cells for illustrative purposes. The number of points chosen strives to achieve a smooth looking plot, and depends on the function plotted and the axis scaling. As a general rule the authors have found that 51 data values produce smooth looking plots for most non-linear functions. Using the Fill Series and Copy/Paste spreadsheet commands make creating the formulas for these extra data points little more work than in the linear case. First, use the Fill Series command to create an extended data grid section. Then create the first y-value formula as before and copy that y-value formula to the extra cells. Graphing the data proceeds the same as for the linear case except that the extra data for the non-linear graph needs to be selected before starting the chart wizard.

The Fill Series command simplifies creating the extra x-values. It extends a series, determined by the first two values, by using the fill handle located at the bottom right corner of a range of selected cells. Create the first two values of the series in separate cells and select those cells. Then place the mouse cursor over the fill handle and left drag until the series is complete. The mouse cursor changes to a narrow plus sign when it is over the fill handle. Click here to view an animation of creating the data section for the exponential function data shown in Figure 8.

This completes the lesson.