Graphing Exercise

Graphing Exercise
A spreadsheet program is a useful tool for scientists to not only compile data but to perform
calculations and create figures (graphs) as well. The ability to create graphs (figures) is one that
all scientists need and one that is no longer done by hand. There are many different types of
graphs – the one most typically used is a scatter plot. In this exercise, you will create three (3)
separate scatter plots using a spreadsheet program of your choice. Commonly used spreadsheet
programs include those by Microsoft® (Excel), Apple® (Numbers), and OpenOffice.org (Calc).
R is an additional software package that can be used for statistical computing and graphics.
Objectives:
1. To learn how to use a spreadsheet program to perform calculations.
2. To learn how to use a spreadsheet program to create figures.
Procedure:
1. Watch the videos and read through the tutorials that have been posted in the Course
Management System (CMS – ANGEL, Blackboard, etc.) on how to create a scatterplot
figure.
2. Enter the following data into the spreadsheet program of your choice:
Preparation Time in hours GPA on a 4.0 scale
12.3 0.082
24.6 0.592
36.9 1.102
49.2 1.612
61.5 2.122
73.8 2.632
3. A common practice is to title each column with the variable name and units in
parentheses. For example, a column of data representing length measurements in
centimeter may be titled: Length (cm)
4. Using the data given, create a graph where Preparation Time is the independent variable
(x-axis) and GPA is the dependent variable (y-axis). This is Figure 1.
This document was prepared by D. Finneran, J. Tierney. Page 2 of 4
5. Remember:
a. All graphs must have a title that clearly states what is being graphed and any
constraints that would cause the graph to look different.
b. All axes must be labeled with the variable that is being plotted with the units that
are being used for plotting.
i. plot the independent variable (the variable you controlled/manipulated) on
the x-axis
ii. plot the dependent variable on the y-axis
c. All plotted data for a given parameter must be plotted using the same symbol.
Different parameters should have different symbols.
d. All symbols must be clearly defined.
e. The data should utilize all of the available space.
f. The data points should not be connected. Remember to add a “trend line”. Any
time you a trend line is present, the trend line equation and R2
value should be
displayed as well.
g. The name of the experiment, the student‟s and partner‟s name (if applicable), and
the date should also appear somewhere on the graph.
This document was prepared by D. Finneran, J. Tierney. Page 3 of 4
6. Enter the following data for the vapor pressure of water (obtained from the CRC
Handbook of Chemistry and Physics) into a spreadsheet program being sure to include a
label at the top of the column:
Vapor Pressure in units of mmHg Temperature in units of °C
4.6 0
6.5 5
9.2 10
12.8 15
17.5 20
18.7 21
19.8 22
21.1 23
22.4 24
23.8 25
31.8 30
42.1 35
55.3 40
71.9 45
92.5 50
149.4 60
233.7 70
355.1 80
525.8 90
760 100
7. Use the data to create a scatterplot where Temperature is the independent variable (xaxis) and Vapor Pressure is the dependent variable (y-axis). This is Figure 2.
a. Determine the function that best fits the data which may not necessarily be a
straight line. Your graph should conform to all the rules of graphing e.g. title,
labels, etc.
8. Using the appropriate formula in the spreadsheet convert the temperatures in oC to the
Kelvin scale. That is, use the spreadsheet to complete the calculation for you. For
example, if I wanted to add 10 to the value in cell A3, I would type “=A3 + 10” in
column D3. I would then click the little box in the lower right corner and drag down to
fill the column. You should know the conversion between Celsius and Kelvin scales. If
you do not, please refer to your textbook. Remember to include a label for the column.
9. Using the appropriate formula convert the temperature in Kelvin to their reciprocal values
(1/T). Remember to include a label for the column.
This document was prepared by D. Finneran, J. Tierney. Page 4 of 4
10. Convert the vapor pressure values in „mm Hg‟ to natural log values. Depending on the
program you are using, the “=LN(cell)” command will typically calculate natural log
values although the command may vary from program to program.
11. Create a scatterplot where 1/T is the independent variable (x-axis) and LN (Vapor
Pressure) is the dependent variable (y-axis). This is Figure 3.
a. Determine the function that best fits the data which may not necessarily be a
straight line. Your graph should conform to all the rules of graphing e.g. title,
labels, etc.
12. Submit all 3 graphs (with your name on them) by the next class period. You are strongly
encouraged to attempt the graphs as soon as possible so that if you run into difficulties,
you can receive assistance before next class.