Graphing with Excel
2. Print the worksheet containing the graph
3.6 OTHER TYPES OF GRAPHS
Most of this chapter has focused on XY scatter graphs, the most common type of graph for engineering work; however, engineers use other types of graphs too.
Other standard types include the following:
• Line graphs
• Column and bar graphs • Pie charts
• Surface plots
CAUTION: A common error is to use a line graph when an XY scatter graph is needed. You will get away using a line graph if the x values in your data set are uniformly spaced. But if the x values are not uniformly spaced, your curve will appear distorted on the line graph. The following example is intended to illus-trate how a line graph will misrepresent your data if your x values are not uni-formly spaced.
EXAMPLE 3.2
The x values in the worksheet shown in Figure 3.33 are calculated as
xi⫹1⫽ 1.2
#
xi (3.1)This creates nonuniformly spaced x values. The y values are calculated from the x values as
yi⫽ 3
#
xi (3.2)so that the relationship between x and y is linear. On the XY scatter graph shown in Figure 3.36 , the linear relationship is evident in the straight-line relationship between x and y .
However, when the same data are plotted on a line graph (shown in Figure 3.37 ), the relationship between x and y appears to be nonlinear.
The apparent nonlinear relationship between x and y in the line graph is an artifact of the line graph, because the actual x values were not used to plot the points on the graph. Excel’s line graph simply causes the y values to be distributed evenly across the chart (effectively assuming uniform x spacing), and the x values (if included when the graph is created) are simply used as labels on the x axis. This can be misleading on a line graph, because the x values are displayed on the x axis, but they were not used to position the data points.
Figure 3.36
The data plotted on and XY scatter graph.
Figure 3.37 The same data plotted as a line graph.
(continued)
Excel’s line, column, bar, and pie charts all require only y values to create the graph. If you provide two columns (or rows) of data, the left column (or top row) will be used as labels for the graph. If you need to plot x and y values on a graph, you must use an XY scatter plot.
Surface Plots
A surface plot takes the values in a two-dimensional range of cells and displays the values graphically. To create these series, the fi rst two values were entered by hand, and then the Fill Handle was used to complete the rest of each series.
The first value for F( x , y ) is calculated in cell B3 using the formula
=SIN($A3)*COS(B$2) , as shown in Figure 3.40 .
The dollar signs on the A in SIN($A3) and the 2 in COS(B$2) allow the formula to be copied to the other cells in the range (both x and y directions). When the formula is copied, the SIN functions will always reference x values in column A, and COS functions will always reference y values in row 3.
Next, the formula in cell B3 is copied to all of the cells in the range B3:Q18, to fi ll the two-dimensional array (Figure 3.41).
Then the array of values is selected before inserting a surface graph using Ribbon options Insert/Charts/Other Charts and selecting the Wire-frame icon in the Surface category (illustrated in Figure 3.42 ).
Figure 3.40
The fi rst calculated cell, cell B3.
Figure 3.39 x and y values.
(continued)
Figure 3.42
Inserting a surface graph into the worksheet.
Figure 3.41
Cell B3 has been copied to cells B3:Q18.
The surface graph is inserted into a window on top of the data (see Figure 3.43 ), but it can be moved.
The basic graph has been inserted, but some better labels will help people understand what they are seeing. First, we add three axis labels and a title, as shown in Figure 3.44 . This was accomplished from the Ribbon using Chart Tools/Layout/
Labels/Chart Title and Chart Tools/Layout/Labels/Axis Titles .
Figure 3.43
The surface graph inserted into the worksheet.
Figure 3.44
The surface graph with axis labels and title.
1.00
0.50
0.00
F(X, Y)
⫺0.50
⫺1.00 3 4
2 5 6
Series1 Series5
Series9 Series13
7 8 9
X
12 13 10 11
1.4 1.5 16 1
Next, we want to replace the labels on the x axis (1 . . . 16) and the y axis (Series1 . . . Series13) with something more meaningful. The x and y values both range from –1 to 2, so we want to get these values into the labels on the x and y axes.
Opening the Select Data Source dialog ( Chart Tools/Design/Data/Select Data ), we can quickly see where the labels on the x and y axes are coming from (Figure 3.45).
The x and y axis labels are coming from the series names ( y axis) and horizontal axis labels ( x axis) that Excel created when the surface graph was created. We need to use the Edit buttons located just above each set of labels to change the series names (one at a time) and horizontal axis labels (all at once) to more meaningful values. The result is shown in Figure 3.46 .
And the updated graph is shown in Figure 3.47 .
Figure 3.45 The Select Data Source dialog.
Figure 3.46 The Select Data Source dialog with updated x and y axis labels.
MATERIALS TESTING Stress–Strain Curve I
Strength testing of materials often involves a tensile test in which a sample of the material is held between two mandrels while increasing force—actually, stress (i.e., force per unit area)—is applied. A stress vs. strain curve for a typical duc-tile material is shown in Figure 3.48 .
During the test, the sample fi rst stretches reversibly (A to B). Then irreversible stretching occurs (B to D). Finally, the sample breaks (point D).
Point C is called the material’s ultimate stress , or tensile strength , and represents the greatest stress that the material can endure (with deformation) before coming Note that the x and y values on the worksheet (column A and row 2) were never used to plot the points on the surface plot. The F( x , y ) values in cells B3 through Q18 were plotted with uniform spacing in the x and y directions. This is a signifi cant limitation of surface plotting in Excel.
Stress (MPa)
apart. The strain is the amount of elongation of the sample (mm) divided by the original sample length (mm).
The reversible stretching portion of the curve (A to B) is linear, and the proportionality constant relating stress and strain in this region is called Young’s modulus , or the modulus of elasticity .
Tensile test data on a soft, ductile sample are listed in Table 3.2 (available elec-tronically at http://www.chbe.montana.edu/Excel ).
To analyze the data we will want to: