PLOTTING A LINE GRAPH
In science, a line graph is used to show the relationship between two quantities, one of which depends on the other. For instance, in Table 1, the data shows that pressure depends on (i.e., is a function of) altitude above sea level. This relationship can be better illustrated if the data is plotted in a line graph. Each point along the line is related to one pressure reading and one altitude measurement.
VARIATIONS OF PRESSURE WITH ALTITUDE
Altitude (km above sea level) Pressure (torr)
0 (sea level) 760
Steps in Plotting
1. Prepare a data table as in Table 1 on a separate sheet of writing paper.
2. Using graph paper provided, draw a line several cm from the left edge of the paper for a vertical axis, and another line several cm from the bottom edge for a horizontal axis.
3. Select each axis. In this case, the pressure is plotted as a function of altitude, so the altitudes (independent variable) will be plotted on the horizontal (X axis), and the pressures (dependent variable) will be plotted on the Y axis.
4. Determine the range of values for each axis by subtracting the smallest value from the large at one. The altitude varies from 0 to 10 km, a range of 10 km. The pressure varies from 198 torr to 760 torr, a range of 502 torr. This is an inconvenient number to work with, and should be “rounded off” to the next convenient larger number, for example, 600 torr. You may want to start the graph below 198 (either 100 torr or 0 torr) and end it at a maximum of 800 torr.
5. Select a convenient scale of units for each axis.
A. Count the number of lines, or divisions, on an axis available on your graph paper.
B. Divide the range by the number of divisions, e.g.:
Horizontal axis: ("\1\0" "km" )/("\2\0" "divisions" ) = (0"\."5 "km" )/(1 "division" ) (or 1 km for every 2 divisions)
Vertical axis: ("\8\0\0" "torr" )/("\3\0" "divisions" ) = ("\2\6\."7 "torr" )/(1 "division" ) (or 100 torr for every 2 divisions)
C. If the result per division is not a convenient number (i.e., one which is easy to plot and read), round it off to a larger more convenient value, e.g.,:
Raise 0.33 0.50; 1.7 2; 3 5; 9.5 10; 13 20 and
26.7 torr becomes 50.0 torr
1 division 1 division
D. Time is well spent selecting an appropriate scale, as your graph should be large enough for you to plot the data with reasonable accuracy and able to be read easily by others.
6. Aassig values to the scale divisions:
A. Begin with 0 or a number smaller than the lowest number to be plotted.
B. Label each major scale division with a number that adds to the previous one by an equal amount, e.g. the horizontal (X axis) is labeled in units of 1 km for every 2 divisions (0, 1, 2, 3 etc), the vertical (Y axis) is labeled in units of 100 torr for every 2 divisions (0,100, 200, 300, etc.)
7. After both axes are labeled numerically, they are then labeled with the names of the plotted variables. Here the Y axis is labeled “Pressure” and the units of pressure (torr) are given in parenthesis after the name of the variable. The X axis is labeled "Altitude" and units (km) are placed in parenthesis after the name of the variable.
8. A point for each set of values is plotted.
A. Locate one value, altitude, on the X axis and follow an imaginary line upward (use a ruler if necessary).
B. Locate the corresponding value on the Y axis and follow an imaginary line horizontally to the right.
C. At the intersection of the two imaginary lines place a dot with a sharp pencil point. Circle the dot so that it can be easily located.
9. Repeat this procedure for each set of values.
10. After there is a dot for each set of data examine the outline of the plotted points.
A. Are the point arranged approximately in a straight line? Then lay a ruler in such a position as to include all the points, or, if not exactly a straight line, set the ruler so that an equal number of points occur on each side of the line, and, equally as far away from it as possible. Draw the line with a pencil.
B. Are the points arranged in a curve? Sketch the curve so as to include the points as a smooth shape with as many points on one side as on the other. Erase the trial lines and draw a single smooth curve.
Entitle the graph at the top of the paper, "Variation of Pressure and Altitude". Figure 1 represents the graphical output expected for the data given in Table 1.
The graph can now be used to determine altitude-pressure relationships that are not given in the data. For example, the average atmospheric pressure at an altitude of 1.5 km can be determined using the graph. First, the point that represents 1.5 km is found on the x-axis and an imaginary line is followed vertically upward unit it intersects the curve. From this point, an imaginary line is followed horizontally to the left until it intersects the y-axis. The pressure represented by the point of intersection of the second imaginary line and the y-axis is about 630 torr. Therefore, the average atmospheric pressure at an altitude of 1.5 km is 630 torr. The process of determining an unknown value that falls between known values on a graph is called interpolation. Use your graph to determine at what altitude the pressure is 425 torr. Write your answer here: ___________________________
Often a line graph may be extended beyond the known values to determine results outside the experimental data. This is called extrapolating the graph. When extending the graph, continue straight lines at the same angle and direction. Curved lines are extended along the same curve. Care must be used with extrapolation, because sometimes other factors may cause a change in shape of the graph line, as conditions amy vary beyond one's experimental values.
GRAPHING WITH MICROSOFT EXCEL WORKSHEETS
Excel is a powerful computer program that will help you to create charts and graphs, do statistical analysis and automate simple calculations for this and future science courses.
The best way to appreciate the utility and applicability of Excel is for you to follow along with an example and make your own Excel worksheet. So, open “Microsoft Excel” from the desktop or the “Start” menu on your computer. Follow along with this example:
Suppose you have a 10-gram ice cube, and you want to know the mass of the ice as it melts over a period of time. As the ice melts and the water drains away, the mass of the ice will decrease with time. You measure the mass of the ice cube every 2 minutes for 30 minutes and get data as listed in the table below. The data below is made up, and may not reflect reality, but the importance of the exercise it to learn how to use MS Excel to organize and analyze data.
Time (minutes) Mass (grams)
Input these numbers into your Excel spreadsheet, but first review the explanation below, as it relates to MS XL 2007.
For MS XL 2007
Excel is divided into cells. Each cell is designated by a column (designated A, B, C….) and a row (designated 1, 2, 3,…). Start entering data in a cell near the top of the sheet, but NOT at the very top and left hand corner. Leave some room for headers or movement of the data if needed later on. Pointing to cell C6, type “Time (minutes)” as a header for that column, and type “Mass (grams)” as a header in the next column in the D6 cell to the right. As in the table on the prior page, enter values for “Time” for the even numbered minutes from 0 to 30 in a single column. Enter the corresponding “Mass” values in the adjacent column to the right. In other words, if the time “0.0” is in cell C7, then the mass “10.0” is in cell D7, and the time “2.0” goes in C8 and mass “9.0” goes in D8, etc.
In order to determine the rate at which the ice is melting, we need to plot the data:
Highlight the data in the two columns you just created. Include text only … DO NOT include the column headings. Click on the “Insert” tab on the toolbar at the top of the page, and icons will appear that represent several options for the types of charts.
Click on “XY (Scatter)” from the several types of graphs. Then select one of the 5 options for the appearance of the graph (try the one with data points and curvy lines).
You should now see the graph you selected appear on the spreadsheet. The graph will need to be dressed up by adding a heading and axis labels.
First you need to get rid of useless and often annoying parts of the graph. You will see a legend labeled “Series 1”. If more than one set of y values were entered, the graph would have more than one line, and legend labels would appear as “Series 2”, “Series 3”, etc. Since there is only one data set in this exercise, click on the “Series 1” legend. A frame will appear around the legend. Press the “Delete” key to remove the legend from the graph.
The graph needs a heading and axes labels. Click on the graph. A frame will appear around the graph and a new set of tabs will appear in the menu. Click on the “Layout” tab of “Chart Tools”. This takes you to a set of icons that will allow you to create a heading and labels for the axes.
Click on the “Chart Title” icon and a pop-up window will open that allows you to locate your title on the chart. Click on “Above Chart” and a frame will appear on the chart. Give your chart a title by typing “Mass of Melting Ice Cube” in the frame.
To insert labels, click on the “Axis Title” icon, then select the “Primary Horizontal Axis Title” (that is the “x” axis) and then select “Title Below Axis”. A box will appear under the “x” axis. Click on the box and type “Time (minutes)” in the box. Repeat the process to create a title for the “Primary Vertical Axis Title” (that is the “y” axis) and select “Rotated Title”. Type “Mass (grams)” in the box that appears to the left of the “y” axis. Make sure to include the units in your labels. Play around with some of the other options to see what they do.
Now we have a graph that shows the mass of the ice cube decreasing over time. But we need more information.
Click on the blank space in your new chart. A frame will appear around the graph and a new toolbar will appear at the top of the page. Click on the “Add Trendline” icon and then on “More Trendline Options …”. A new window will appear with lots of options. Select “Linear” and then check two boxes at the bottom of the window: “Display Equation on chart” and Display R-squared value on chart”.
Close the window and a line will appear through the data points in your graph. Also, an equation and an R2 value will appear. Click on the equation and then drag it to an open area in the chart.
What you have just done with the 2007 version of XL is to draw the best fit line through your data points. This line DOES NOT go through all points, but it is the line which comes closest to ALL points. This best fit line also describes a mathematical equation of the form y = mx + b, where x and y are your axes, m is the slope of the line, and b is the y intercept. You should have gotten the equation y = -0.3343x + 9.889. The slope of the line, m, is –0.3343, where m is equal to the change in y value divided by change in x value (∆y ÷ ∆x). What this means is that the mass of the ice block changes by –0.3343 gram (the mass decreases) per each minute. The y intercept is 9.889. This is the point where your line crosses the y axis at time zero. In other words, it is the starting point where x = 0.0. The R2 value gives you an idea of how close your points are to the best fit line. A value of 1.00 means that ALL points are EXACTLY on the line. A number close to 1.00 (e.g., from 0.95 to 0.99) means that there is an excellent correlation between the data itself and the line describing the data.
With either version of MS Excel, you can conduct repetitive calculations. For example, suppose that you want to know the mass of the ice block in ounces rather than grams. You know that 1 g = 0.0353 oz. (Is that the correct value?). Rather than multiply each mass in your table by 0.0353, let Excel do it for you.
Go to the next column to the right of the mass column and click on the first cell (i.e., if the first mass is in cell D7, then click on cell E7).
Type “=” in that cell. This lets Excel know that you are going to write an equation in the cell.
Click on the mass value (D7 in our example) and note that the cell location, “D7” will appear next to the “=” sign. Now, then type “*0.0353” (*is the symbol for multiplication in Excel). Notice that the dialogue box at the top of the page says “=D7*0.0353”, which lets Excel know that you want to multiply the value in cell D7, which is 10 g, by 0.0353. Hit “enter”. The value 0.353 will appear in cell E7.
To repeat that operation so as to convert all D values in grams to ounces, click on cell E7. Draw the pointer over the lower right hand corner of the cell and the pointer shape will change to a dark plus sign. Click and hold while you drag the dark plus sign down to the end of the column, which in this case is cell E22. When you release the mouse button, the mass values in ounces will appear in all the cells above your pointer in column E.
V. PRACTICE EXERCISES
Complete the following exercises according to your instructor’s directions. Submit the completed assignments (pages 8 to 10) as your report for this experiment, and include the Microsoft XL graphs for those exercises that call for graphing.
1. Given the following points to be plotted along one axis of a graph, select the most reasonable range for the axis:
A. Data: l3, 29, 36, 49, 58, 66
a) 0 - 1000 b) 0 - 80, c) 13 - 66 d) 13 - 70
B. Data: 0.0013, 0.0029, 0.0036, 0.0049, 0.0066
a) 0 - 0.10 b) 0 - 0.0080 c) 0.0013 - 0.0066 d) 0.0013 - 0.0070
For choice (b) of question A above, if 35 lines (divisions) were available, what would be your choice of a value for each line? (Remember to round up to a convenient value.)
For choice (b) of question B above, what is your choice of a value per division if 40 divisions are available?
Given the highest value to be plotted is 0. 580 and the lowest value as 0.120, and a total of thirty lines (boxes) available for plotting, what would be the value that should be chosen for each line?
a) 0.0153 b) 0.015 c) 0.02 d) 0.2
5. In the preparation of a calibration curve in the laboratory, the following data were collected.
Concentration (mg/l00ml) (X axis) Absorbance (Y axis)
Using Excel, plot the points and construct a standard curve for these values. Label the axes correctly.
From the data below, plot a smooth curve in Excel for “Standard Concentration of Dioxol (mg/100mL)” as the horizontal axis and “% Transmittance” as the vertical axis. Label each axis and give a title to the graph. You have just made a calibration curve … a handy skill for other assignments in this and other courses.
Dioxol (mg.100mL) % Transmittance
The data below shows the percent extraction of HNO3 solutions by the Zano method. Plot a smooth curve in Excel for the “% HNO3 Extracted” as the vertical axis and “Molarity of HNO3” as the horizontal axis. Label each axis and give a title to the graph.
% HNO3 Extracted Molarity of HNO3 (M)
According to this graph, can 80% HNO3, be extracted? _____________
The % HNO3, extracted of a 0.700 molar sample was estimated from the graph. This is called a (an) ___________________ value?
What is the concentration (M) of HNO3 at maximum % extraction? ___________
When the HNO3 concentration is 0.350 molar, what is the % extraction? _____________________________
8. Complete the following table:
°C -30 -10 20 40
°F -4 50 86
Use Excel to plot a graph representing the above data correlating the temperature in Celcius (°C) and Fahrenheit (°F) scales.
Determine the slope and the intercept for the resulting graph.
Write the equation relating °F to °C:
Use the graph and the equation above to convert the following °C to °F:
16°C °F = ___________ __________
-40°C °F = ___________ __________
9. Suppose that you use a spectrometer to measure the transmittance (T) of light through solutions of varying concentrations. You collect the following data:
Concentration (M) T
Transmittance is a good value, but a better way to analyze the results is to determine the quantity of light that was absorbed by the solution. So we first need to convert the values for transmittance to absorbance, A, by using the formula A = -log(T) (don’t worry about what the math means for now). Use the Excel equation function you used earlier in this experiment to convert transmittance to absorbance and then plot the absorbance (y-axis) vs. concentration (x-axis). Select the best trend line and find the equation of this line. Report the slope, y intercept and R2 value.
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.