Graphical Analysis of Data Using Microsoft Excel Lab Reports

Santa Monica CollegeChemistry 11
Graphical Analysis of Data using Microsoft Excel©
Introduction
Graphs are useful tools that can elucidate key relationships in collected experimental data.
First, plotting a graph provides a visual image of data and any trends therein. Second, upon
analysis, they can provide us with the ability to predict the results of any changes to the system.
In this exercise, you will learn how to use Microsoft Excel© to manipulate and graph quantitative
data in a variety of ways. If needed, you can download a free copy of Excel 365 to your
personal device from the freely available Office 365 (provided by SMC) by signing in at:
http://www.smc.edu/TechnologyResources/StudentEmail/Pages/Office365.aspx
You can also use Microsoft Excel© 2016 or 2019, which are very similar. Furthermore, Excel
365 is installed on all computers available for student use in the various SMC computer centers.
Part 1: Simple Linear XY Plot
The relationship between a set of x-data and y-data can be easily determined by creating a
simple XY plot. The x-data – which is controlled by the experimenter – is gradually changed,
and the effect of these changes on the y-data may then be readily observed in the plot. Often
these plots will produce a straight line. Recall that whenever there is a direct, linear relationship
between the plotted x- and y-data, that data may be fitted to the equation of a line with the form
y = mx + b, through a technique known as linear regression. This is shown in Figure 1 below.
This equation expresses the mathematical relationship between the x- and y-data, and allows
for the prediction of unknown values. In Part 1, you will learn how to prepare a simple XY plot
and perform a linear regression using Excel.
best-fit line
y data
Best-fit line Equation: y = mx + b
b
y
x
b = y-intercept
m = slope = y/x = y2-y1/x2-x1
x data
Figure 1
Scenario – An experiment is designed to measure the pressure of 0.100 moles of neon gas at a
variety of different temperatures, while keeping the gas in a sealed 5 L steel container:
Temperature (K)
283
323
363
403
443
Pressure of Neon (torr)
345
409
446
512
542
a) Launch Excel and open a blank workbook.
Graphical Analysis of Data using Microsoft Excel
Page 1 of 8
Santa Monica College
Chemistry 11
b) Enter the data into the first two columns in the spreadsheet.
•
•
Reserve the first row for column labels.
The x values must be entered to the left of the y values in the spreadsheet. Remember
that the independent variable (the one that the experimenter has control of) goes on the
x-axis while the dependent variable (the measured data) goes on the y-axis.
c) Highlight the set of data – not the column labels – that you wish to plot (Figure 2). Click on
the Insert tab (top left), then go to Charts, then Scatter (Figure 3). Choose the scatter
graph that shows data points only, with no connecting lines – the option labeled Scatter
with Only Markers (Figure 4).
Figure 2
Figure 3
Figure 4
A plot should appear on your Excel screen, which is a preview of your graph (Figure 5).
Figure 5
d) If all looks well, it is time to add titles and label the axes of your graph (Figure 6, next page).
•
•
•
•
•
First, click inside the chart.
Switch to the Design tab, and click Add Chart Element > Chart Title > Above Chart.
The graph should be given an appropriate title that starts out “Y versus X” followed by a
brief description of your system.
Again, click on Add Chart Element > Axis Titles (first select Primary Horizontal Axis
Title and then Primary Vertical Axis Title) to add labels to the x- and y-axes. Note that it
is important to label axes with both the measurement and the units used.
To change the titles, click the text box for each title, highlight the text and type in your
new title.
Graphical Analysis of Data using Microsoft Excel
Page 2 of 8
Santa Monica College
Chemistry 11
Figure 6
e) Your next step is to add a trendline to these plotted
data points. A trendline represents the best possible
linear fit to your data. To do this you first need to
“activate” the graph by clicking on any one of the data
points. When you do this, all the data points will
appear highlighted.
•
•
•
•
f)
Click the Chart Elements button
next to the
upper-right corner of the chart.
Check the Trendline box.
Click More Options. This will display the option
shown in Figure 7.
Notice that the “Linear” button is already selected.
Now select the “Display Equation on Chart” box
and the “Display R-squared value on Chart” box.
Then click “Close”.
The equation that now appears on your graph is the
equation of the fitted trendline. The R2 value gives a
measure of how well the data is fit by the equation.
The closer the R2 value is to 1, the better the fit.
Generally, R2 values of 0.95 or higher are considered
good fits. Note that the program will always fit a
trendline to the data no matter how good or awful the
data is. You must judge the quality of the fit and the
suitability of this type of fit to your data set.
Figure 7
g) Click on your graph, then print out a full-size copy and attach it your Excel Report form. The
title, axis labels, trendline, equation and R2 value should all be easy to see on your graph.
Graphical Analysis of Data using Microsoft Excel
Page 3 of 8
Santa Monica College
Chemistry 11
h) Notice that by graphing just five sets of measured values, a relationship is established
between gas pressure and temperature. The graph contains a visual representation of that
relationship (the plot) as well as a mathematical expression of it (the equation). It can now
be used to make certain predictions. For example, suppose the sample of neon gas is
cooled until its pressure drops to 181 torr. You are asked to determine the gas temperature.
Note that the value “181 torr” falls outside the range of the plotted data. How can you find
the temperature if it doesn’t fall between the known points? There are two ways to do this.
Method (1): Extrapolate the trendline and estimate where the point on the line is.
•
•
•
Click again on the Chart Elements button
next to the upper-right corner of the chart,
then on Trendline and then More Options.
In the section labeled “Forecast” enter a number in the box labeled “Backward”, since
we want to extend the trendline the backward x direction. To decide what number to
enter, look at your graph to see how far back along the x-axis you need to go in order to
cover the section where pressure = 181 torr. After entering an appropriate number, click
“Close”, and the line on your graph should now be extended in the backward direction.
Now use your graph to estimate the x value by envisioning a straight line down from y =
181 torr to the x-axis. Record this value on your report.
Method (2): Plug this value for volume into the equation of the trendline and solve for the
unknown temperature. Record your answer and show your work for this calculation on your
report. This method is more precise than extrapolating and “eye-balling” from the graph.
Part 2: Two Data Sets with Overlay
Scenario – A spectrometer is used to measure the light absorbance of several solutions
containing different quantities of a red dye. Two sets of data are collected:
Dataset A
Dataset B
Amount of Dye (mol)
Absorbance (no units)
Amount of Dye (mol)
Absorbance (no units)
0.100
0.200
0.300
0.400
0.500
0.600
0.700
0.750
0.049
0.168
0.261
0.360
0.470
0.590
0.700
0.750
0.800
0.850
0.900
0.950
0.620
0.440
0.285
0.125
You would like to see how these two sets of data relate to each other. To do this you will have
to place both sets of data, as independent relationships, on the same graph. Note that this
process only works when you have the same axis values and magnitudes.
a) Enter this new data on a fresh page (Sheet 2) into 4 columns in Excel. Be sure to label your
data columns and remember to enter the x values to the left of the y values for each dataset.
b) First, plot Dataset A as a simple XY plot, following the same procedure as you did in Part 1.
Fit a trendline to this data, and obtain the equation of this line.
Graphical Analysis of Data using Microsoft Excel
Page 4 of 8
Santa Monica College
Chemistry 11
c) Now add Dataset B to this graph.
•
•
•
•
Activate the graph by clicking on one of the plotted data points.
Right-click the chart, and then choose Select Data. The Select Data Source box
appears on the worksheet with the source data of the chart.
Click the Add tab and type “Dataset B” for the Series Name.
Click the little icon on the right of the box “Series X values”, then highlight the x-axis
values of Dataset B. Press enter, then repeat this procedure for the “Series Y Values”,
highlighting the y-axis values of Data B. For each of these steps, you should see the
display shown in Figure 8. Note that slight differences may appear due to the version of
Microsoft Excel installed on your computer.
Figure 8
d) Click OK twice to return to the main Excel window. At this point you should see the new
data points similar to what is shown in Figure 9. You can now independently analyze this
Dataset B by inserting a trendline and obtaining the best-fit equation.
Figure 9
e) Complete your graph by adding axis labels (with units) and an appropriate chart title. Then
click on your graph, print out a full-size copy, and attach it to your Excel Report form. The
title, axis labels, trendlines and best-fit equations for both datasets should all be easy to see
on your graph.
f)
Notice that if these trendlines were extrapolated, they would intersect. Determine the values
of x and y for the point of intersection using simultaneous equations. Record your answers
and show your work for this calculation on your report.
Graphical Analysis of Data using Microsoft Excel
Page 5 of 8
Santa Monica College
Chemistry 11
Part 3: Statistical Analysis and Simple Scatter Plots
When many independent, repeated measurements are made for one variable, there is inevitably
some scatter (noise) in the data. This is usually the result of random errors over which the
experimenter has little control.
Scenario – Ten different students at two different colleges each measure the sulfate ion
concentration in a sample of tap water (measurements are in parts per million, or ppm):
Santa Monica College (SMC)
Sulfate Concentration (ppm)
35.9
41.4
32.8
35.1
33.2
37.6
27.7
36.6
35.0
39.3
El Camino College (ECC)
Sulfate Concentration (ppm)
45.1
34.2
36.8
31.0
40.7
29.6
35.4
32.5
43.5
38.8
Simple statistical analyses of these datasets might include calculations of the mean and
median concentration, and the standard deviation. The mean ( x ) is simply the average
value, defined as the sum () of each of the measurements (xi) in a data set divided by the
number of measurements (N):
x=
x
i
N
The median (M) is the midpoint value of a numerically ordered dataset, where half of the
measurements are above the median and half are below. The median location of N
measurements can be found using:
M = (N + 1) 2
This formula yields an integer that represents the value corresponding to the median location in
an ordered distribution of measurements. For example, in the set of numbers (3 1 5 4 9 9 8) the
median location is (7 + 1) / 2, or the 4th value. When applied to the numerically ordered set (1 3
4 5 8 9 9), the number 5 is the 4th value and is thus the median – three scores are above 5 and
three are below 5.
Standard deviation (s) is a measure of the variation in a dataset, where:
(x − x )
s=
2
i
N −1
Thus, to find s, subtract each measurement from the mean, square that result, add it to the
results of each other difference squared, divide that sum by the number of measurements minus
one, then take the square root of this result. The larger this value is, the greater the variation in
the data, and the lower the precision in the measurements.
Graphical Analysis of Data using Microsoft Excel
Page 6 of 8
Santa Monica College
Chemistry 11
While the mean, median and standard deviation can be calculated by hand, it is often more
convenient to use a calculator or computer to determine these values. Microsoft Excel© is
particularly well suited for such statistical analyses, especially on large datasets.
a) Enter the data acquired by the students from SMC only into a single column of cells on a
fresh page (Sheet 3) in Excel. Then in any empty cell (usually one close to the data cells),
instruct the program to perform the required functions on the data. To compute the mean or
average of the data entered in cells a1 through a10, for example, you must:
•
•
•
click the mouse in an empty cell
type “=average(a1:a10)”
and press return
To obtain the median you would instead type “=median(a1:a10)”. To obtain the standard
deviation you would instead type “=stdev(a1:a10)”.
b) Record on your report:
•
•
The Excel-calculated mean, median and standard deviation for the SMC dataset.
As an additional exercise (for fun?), calculate the standard deviation of this dataset by
hand, and compare it to the value obtained from using Excel.
Rejecting Outliers
Do all the measurements in the SMC dataset look equally good to you, or are there any values
that do not seem to fit with the others? If so, are you allowed to reject these measurements?
Outliers are data points which lie far outside the range defined by the rest of the measurements
and may skew your results to a great extent. If you determine that an outlier resulted from an
obvious experimental error (e.g., you incorrectly read an instrument or prepared a solution), you
may reject the point without hesitation. If, however, none of these errors is evident, you must
use caution in making your decision to keep or reject a point. One rough criterion for rejecting a
data point is if it lies beyond two standard deviations from the mean or average.
c) Using the above criteria, determine if there are any outliers in the SMC dataset.
•
•
Record your calculations and identify any outlier measurements on your report.
Then, excluding the outliers, re-calculate the mean, median and standard deviation of
this SMC dataset (using Excel).
Rejecting data points may not be done just because you want your data to look better. If
you choose to reject an outlier for any reason, you must always clearly document in your lab
report:
•
•
•
that you did reject a point
which point you rejected
why you rejected it
Failure to disclose this could constitute scientific fraud.
Graphical Analysis of Data using Microsoft Excel
Page 7 of 8
Santa Monica College
Chemistry 11
Graphing a Scatter Plot
Experimental
Measurements
Unlike the linear XY plots created so far, a scatter plot simply shows the variation in
measurements of a single variable in a given dataset, i.e., it supplies a visual representation of
the “noise” in the data. The data is usually plotted in a column, as shown in Figure 10. Note
that datasets with a greater degree of scatter will have a higher standard deviation and consist
of less precise measurements than datasets with a small degree of scatter.
Data set 1
Data set 2
Figure 10
To obtain such a plot using Excel, all the x values for each dataset must be identical. Thus, let
the SMC measurements be assigned x = 1, and let x = 2 for all the ECC measurements:
Measurements by Students from SMC
SMC
Conc (ppm)
1
35.9
1
41.4
1
32.8
1
35.1
1
33.2
1
37.6
1
27.7
1
36.6
1
35.0
1
39.3
Measurements by Students from ECC
ECC
Conc (ppm)
2
45.1
2
34.2
2
36.8
2
31.0
2
40.7
2
29.6
2
35.4
2
32.5
2
43.5
2
38.8
d) Enter the data as shown above into four columns of your spreadsheet.
•
•
•
Plot the SMC dataset as a simple XY plot (as learned in Part 1).
Now add the ECC dataset to this graph by applying the same steps you used to create
your graph in Part 2.
Add appropriate axis labels and a title. You may want to add a Legend (under Chart
Elements) to identify which dataset was produced by which college. You may also want
to adjust the x-axis and y-axis scales to improve the final look of your graph.
e) Click on your graph, print out a full-size copy, and attach it to your Excel Report form. The
title, axis labels, and both datasets should all be easy to see on your graph.
•
Which dataset (SMC or ECC) shows more scatter? Which contains the more precise
measurements? Which will have the larger standard deviation?
Graphical Analysis of Data using Microsoft Excel
Page 8 of 8
Santa Monica College
Chemistry 11
The Densities of Solids and Solutions
Objectives
The objectives of this laboratory are as follows:
•
To determine the density of distilled water using different types of volumetric glassware in
order to compare the precision of this glassware.
•
To determine the density of a de-gassed soda via graphical analysis.
•
To determine the density of an unknown metal using the technique of water displacement,
and use this value to identify the metal.
•
To use the program Microsoft Excel© to perform graphical analysis of experimental data.
Background
Density is a fundamental physical property of matter. Physical properties are characteristics of
a substance that can be observed or measured without changing its chemical identity. Other
physical properties include melting point and solubility. In general, since different substances
have unique densities, determining the density of an unknown substance can help identify it.
Density is also an intensive property of matter. An intensive property is one that does not
depend of the amount of matter present. In other words, a substance will have the same
density whether its quantity is large or its quantity is small. An extensive property, in contrast, is
one that does depend on the amount of matter present.
Density is specifically defined as the ratio of a substance’s mass to its volume:
Density =
Mass
Volume
The S.I. unit of density is kg/m3, but in chemistry it is more often expressed in units of g/cm3 for
solids, and g/mL for liquids and solutions. Note that while both mass and volume are extensive,
since density is a ratio of these properties, density is intensive.
In this lab, several different techniques will be employed to determine the density of water (in
Part A), a de-gassed soda (in Part B) and an unknown metal (in Part C). In Parts A and B of
this lab, the experimental data collected will be graphically analyzed, both to provide a visual
image of the data (and any trends therein) and to yield important physical values (density). The
program Microsoft Excel© will be used for this purpose. Students should already have explored
the graphing capabilities of Excel in a separate exercise, and those skills learned will be applied
here. In addition, in Part A a study of density datasets will be performed in order to explore the
concept of precision and the statistical nature of experimental data.
The Densities of Solids and Solutions
Page 1 of 6
Santa Monica College
Chemistry 11
Part A: In this section of the lab, datasets of water density values will be experimentally
obtained and then analyzed, in order to explore the concept of precision and the statistical
nature of experimental data.
Density
Specifically, the density of distilled water will be determined using measurements obtained from
three different types of volumetric glassware (VG): a 50-mL buret, a 100-mL graduated cylinder
and a 10-mL volumetric pipet. Measurements performed by each pair of students in the entire
class will be pooled so that a large ensemble of density values is acquired for each type of
glassware used. The three density datasets will be displayed in a scatter plot, as shown below:
VG 1
VG 2
VG 3
Volumetric Glassware (VG) Used
The datasets will be analyzed for outliers, which will be removed if identified. Recall that when
measurements in a dataset are closely examined, occasionally one or more values may appear
not to “fit in” with the others. These points are called outliers – values that occur far outside the
range defined by the rest of the measurements. One rough criterion for identifying an outlier is
that it lies beyond two standard deviations from the average value. Such values may be
legitimately excluded from a dataset, as they can skew results to a great extent.
Finally, simple statistical analyses of the three datasets will be performed, including calculations
of average density and standard deviation. An average value ( x ) is defined as the sum () of
each of the measurements (xi) divided by the number of measurements (N):
x=
x
i
N
Standard deviation () is defined as:
 =
(x − x )
2
i
N −1
Standard deviation indicates the degree to which a set of measurements deviate from the
average value. Datasets with a large amount of scatter will have a higher standard deviation
and are associated with less precise measurements compared to datasets with little scatter
(greater reproducibility). Thus, using these results, the precision of the measurements obtained
using the three types of volumetric glassware can be compared.
The Densities of Solids and Solutions
Page 2 of 6
Santa Monica College
Chemistry 11
Part B: In this section of the lab, the density of a de-gassed soda will be determined via
graphical analysis of a series of mass and volume data. A de-gassed soda is soda with the
gaseous carbon dioxide removed, typically by gently heating and stirring an open container of
soda over several days.
Using a buret to dispense precise solution volumes, the masses of several increasingly larger
volumes of a de-gassed soda will be measured. This collected mass and volume data will then
be plotted on a graph of “Soda Mass versus Soda Volume” using Microsoft Excel©. A best-fit
trendline will be applied to the plotted data (via linear regression), and the equation of the line
obtained.
Mass
Best-fit line
y
x
Volume
Best-fit line equation: y = mx + b
where b = y-intercept and m = slope
The y-intercept (b) is the point where the line crosses the y-axis. In this experiment, the value of
b should be equal to zero. This is because if there is no volume, the mass must also be zero.
However due to random experimental error, the best-fit line might not pass exactly through the
origin, but it should be quite close.
The slope of the line (m) is the change in the y-axis values (y) divided by the change in x-axis
values (x):
m=
y y1 − y 2
=
x x1 − x2
However according to the graph, since y is actually the change in mass (mass), and x is
actually the change in volume (volume), the slope of the best-fit line will yield the density of the
soda:
m=
The Densities of Solids and Solutions
y
mass
=
= density
x volume
Page 3 of 6
Santa Monica College
Chemistry 11
Part C: In this section of the lab, the density of an unknown metal will be determined and the
metal identified using this experimental value. Although a simple approach is used, this method
can yield density results accurate to 0.1%.
Using a capped glass vial, the following series of four mass measurements will be obtained:
(A) empty vial
(B) vial + metal
(C) vial + metal + water
(D) vial + water
The difference in masses A and B yields the mass of the metal sample. The volume of the
metal may be obtained by taking the difference between the water volumes in C and D
(technique of water displacement – Archimedes Principle). However, these water volumes must
first be calculated using the water masses and the known density of water (see table below).
Finally, density can be calculated using the metal mass and metal volume.
Density of Liquid Water Measured for a Range of Temperatures
(obtained from the CRC Handbook of Chemistry and Physics, 53rd edition)
Temperature (C)
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Density (g/mL)
0.9989
0.9988
0.9986
0.9984
0.9982
0.9980
0.9978
0.9975
0.9973
0.9970
0.9968
0.9965
0.9962
0.9959
In order to identify the unknown metal, the experimentally determined density must be
compared to the true (accepted) densities of several known metals. The percent error between
this experimental value (EV) and the true density value (TV) of the metal will also be calculated.
Percent Error =
| EV − TV |
 100
TV
A more accurate experimental value will yield a lower percent error (< 5% is desirable) than a less accurate value. The Densities of Solids and Solutions Page 4 of 6 Santa Monica College Chemistry 11 Procedure Part A: The Precision of Volumetric Glassware Materials and Equipment: Distilled water (in wash bottle), 100-mL graduated cylinder, 50-mL beaker, 50-mL buret, 10-mL volumetric pipet, pipet bulb, buret stand, electronic balance 1. Weigh a dry 50-mL beaker on an electronic balance, and record this mass on your report form. You will use this beaker and the same electronic balance for Steps 2, 3 and 4. 2. Graduated Cylinder: Fill the 100-mL graduated cylinder with slightly more than 10 mL of distilled water from the wash bottle, then record the actual volume used to the correct number of significant figures. Carefully transfer the distilled water into the small pre-weighed 50-mL beaker, and measure and record the combined mass. When finished, empty the distilled water out of the small beaker, then carefully dry it. 3. Volumetric Pipet: Your instructor will demonstrate the correct use of the volumetric pipet and pipet bulb at the beginning of the lab period. Use the volumetric pipet to transfer precisely 10.00 mL of distilled water from the supply in the wash bottle (with the top removed) into the small pre-weighed 50-mL beaker. Record the volume used and the combined mass of the beaker and water. Note that the pipet measures volume to 2 decimal places. Again, empty the water out of the small beaker when finished, then carefully dry it. 4. Buret: Fill the buret about half-way with distilled water from your wash bottle, then let 1-2 mL of this water drain out of the tip into the sink. Measure and record the buret “initial” reading to the correct number of significant figures. Now drain slightly more than 10 mL of the water from the buret into the small pre-weighed 50-mL beaker. Measure and record the buret “final” reading to the correct number of significant figures. The actual volume used is the difference between the final and initial buret readings – record this value as well as the combined mass of the beaker and water on your report form. 5. When finished, clean up as directed by the instructor. 6. For each of the three sets of data you collected (using the graduated cylinder, volumetric pipet and buret), calculate the density of distilled water to the correct number of significant figures. Then share your three density values with all the students in your lab section, and record the results of the entire class on your report form. Part B: The Density of a De-gassed Soda Materials and Equipment: Degassed soda, distilled water (in wash bottle), 100-mL beaker, small funnel, 50-mL buret, 50-mL Erlenmeyer flask, buret stand, electronic balance 1. Prepare the buret by first rinsing it with distilled water, then rinsing it with a small quantity of the soda, as demonstrated by the instructor. 2. Obtain about 70 mL of degassed soda in a medium 100-mL beaker. Using the funnel, fill the buret with soda (from the beaker) to just above the zero mark, then drain out some of the soda in the sink until the meniscus is between 1 and 5 mL. Secure the buret firmly in place with the buret clamp/stand, and record the initial buret reading to the correct number of significant figures. The Densities of Solids and Solutions Page 5 of 6 Santa Monica College Chemistry 11 3. Weigh a dry 50-mL Erlenmeyer flask on an electronic balance, and record this mass. Use this same balance for all subsequent mass measurements. 4. Carefully drain 5-6 mL of the soda from the buret into the 50-mL flask, and record the new buret reading. Now measure and record the combined mass of the flask and soda. 5. Do not pour the soda out of the Erlenmeyer flask! Next, add an additional 5-6 mL of soda to the flask from the buret. Again, measure and record the new buret reading and the new combined mass of the flask and soda. 6. Repeat Step 5 four more times. You will have obtained a total of six measurements involving increasing larger amounts of soda when you are finished. Then clean up as directed by the instructor. Part C: The Density of an Unknown Metal Materials and Equipment: Unknown metal sample, empty capped glass vial, distilled water (in wash bottle), electronic balance, thermometer 1. Obtain an unknown metal sample from your instructor. Record the ID Code of the metal on your report form. 2. Carefully examine the empty capped glass vial. The cap on this vial should have a small hole pierced through it. This hole will allow air and excess water to be expelled from the vial. Weigh this empty, dry capped vial using an electronic balance. Use this same balance for all subsequent mass measurements. 3. Add the entire sample of your unknown metal to the empty vial and weigh it again (with cap). 4. Now fill the vial (with the metal still in it) to the brim with distilled water. Gently tap the vial to remove any air that might be trapped between the metal pieces. Place the cap on firmly, pressing out excess water. No air bubbles should be visible under the cap. Wipe off any drops of water on the outside of the vial, and then weigh it. 5. Next, remove the metal from the vial and then fill it to the brim with distilled water only. Place the cap on firmly, wipe off excess water, and weigh. Again, no air bubbles should be visible under the cap. 6. Finally, use the thermometer to measure the temperature of the water in the vial. Record this value on your report form, and use it to obtain the density of water from the table on Page 4. 7. When finished, dry the metal sample and return it to your instructor, and clean up as directed. The Densities of Solids and Solutions Page 6 of 6 Santa Monica College Chemistry 11 Gravimetric Analysis of an Unknown Sulfate Objectives The objectives of this laboratory are as follows:   To experimentally analyze an unknown sulfate salt via a precipitation reaction, using the techniques associated with Gravimetric Analysis to collect and weigh the precipitate, and To calculate the percentage by mass of SO4-2 in the unknown sulfate salt via a stoichiometric analysis of the collected precipitate, and then use this percentage to identify the metal “M” present in the sulfate salt. Background Gravimetric analysis is a quantitative method for accurately determining the amount of a substance by selective precipitation of the substance from an aqueous solution. The precipitate is separated from the remaining aqueous solution by filtration and is then weighed. Assuming that the chemical formula for the precipitate is known and that the precipitation reaction goes all the way to completion, then the mass of the substance in the original sample can be determined. In this experiment, the percentage by mass of sulfate in an unknown sulfate salt will be determined by gravimetric analysis. First, a pre-weighed sample of the unknown sulfate salt will be dissolved in water. Next, an excess of aqueous barium chloride is added to the aqueous solution of the unknown salt. This will result in the precipitation of all the sulfate ions as barium sulfate: Metal sulfate (aq) + Barium chloride (aq) → Barium sulfate (s) + Metal chloride (aq) The barium sulfate precipitate is collected by filtration, dried and weighed. Since barium chloride is added in excess, and since the precipitation reaction goes to completion, we can assume that all of the sulfate is transferred from the original unknown sample to the precipitate. The mass of sulfate in the collected BaSO4 precipitate can be calculated via its percent composition. This also yields the mass of sulfate in the original unknown since: mass of sulfate in the precipitate = mass of sulfate in the unknown sample Finally, using the mass of sulfate along with the initial mass of unknown used, the percentage by mass of sulfate in the original sample may now be calculated. In order to obtain the best results, the collected BaSO4 crystals should be as large as possible. This considerably aids the filtration process (larger crystals are less likely to be pass through the filter paper), and it also minimizes the amount of impurities adsorbed onto the crystals (smaller surface area). In general, larger crystals are obtained when the rate of precipitation is as low as possible. The rate of precipitation is minimized by slowly adding the BaCl2 solution to the aqueous mixture containing the unknown salt while continuously stirring the mixture. The rate of precipitation can be decreased even further by slightly increasing the solubility of the BaSO4. This may be achieved by lowering the pH with 6M HCl and by increasing the temperature. The resulting decrease in the yield of the BaSO4 is insignificant. Gravimetric Analysis of an Unknown Sulfate Page 1 of 3 Santa Monica College Chemistry 11 Procedure Chemicals Unknown solid sulfate salt, 6M HCl solution and 0.1M BaCl2 solution Equipment 250-mL beaker, analytical balance, stirring rod, 100-mL graduated cylinder, stand with ring clamp, wire screen, Bunsen burner, wash bottle with distilled water, crucible and lid, crucible tongs, ash-less filter paper, large funnel, 500-mL Erlenmeyer flask, clay triangle Safety Be very careful when handling 6M HCl (aq). If this acid comes in contact with your skin or eyes you should immediately rinse the affected area with water for several minutes. Also remember that items heated in the Bunsen burner are very hot (especially the crucible), and to allow ample time for them to cool before touching. Instructions 1. Weigh a clean, dry 250-mL beaker to the nearest 0.001 g using the analytical balance, and record this mass on your lab report. Next, add 0.30 – 0.35 grams of your unknown sample to the beaker. Record the combined mass of the beaker plus sample on your lab report. 2. Add 50 mL of distilled water, followed by 20 drops of 6M HCl (aq), to the sample in the beaker. Stir the contents of the beaker until the sample has entirely dissolved. Leave the stirring rod in the beaker. 3. Obtain a stand with a ring clamp from the back of the lab. Place your wire screen on the ring and the beaker containing your dissolved sample on the wire screen. Use the Bunsen burner to heat the solution until it is nearly (but not quite) boiling. Turn the Bunsen burner off before the solution boils. 4. While heating the solution, measure out 25 mL of 0.1M BaCl2 (aq) using a 100-mL graduated cylinder. The graduated cylinder used should be clean (rinse with distilled water) but does not need to be dry. 5. Slowly add small portions of BaCl2 (aq) to the beaker containing the hot solution. You should observe the formation of a white precipitate of BaSO4 (s). Stir the contents of the beaker as you add the BaCl2 solution. The addition of the BaCl2 must be performed very slowly – this step should take you at least 3 minutes to complete! When finished, rinse any precipitate that remains on the stirring rod into the solution with a small amount of distilled water, and then allow the precipitate to settle in the beaker for about 20 minutes. 6. While the precipitate settles, prepare your crucible by heating it in the hottest part of the Bunsen burner flame for about 2 minutes (use the crucible tongs). Repeat with the lid. Place the hot crucible and lid on the metal base of the stand to cool. Once they have cooled to room temperature weigh the crucible without the lid using the analytical balance, and record this mass on your lab report. You do not need to weigh the lid. Gravimetric Analysis of an Unknown Sulfate Page 2 of 3 Santa Monica College Chemistry 11 7. Obtain a piece of ash-less filter paper from your instructor and fold it into quarters. Open the folded paper into a cone and place it into your large funnel. Wet the filter paper with a small amount of distilled water so that it adheres to the funnel. Sit the funnel in the mouth of a 500-mL Erlenmeyer flask, which will be used to collect the filtrate. 8. After 20 minutes has passed, slowly pour the mixture containing the BaSO4 precipitate down your stirring rod into the funnel. Be careful that the level of liquid in the funnel is never more than three-fourths of the way to the top of the filter paper. When the transfer is complete use your wash bottle (filled with distilled water) to rinse the residual precipitate from the beaker and the stirring rod into the funnel. 9. Wear gloves for this step. After all the liquid has drained from the funnel, very carefully press the top edges of the filter paper together, and gently fold the filter paper into a compact package that will fit into the crucible. It is important that you do not use too much force in order to avoid tearing the filter paper. Place the folded filter paper into the crucible. 10. Take your stand, ring clamp and Bunsen burner over to a fume hood. Place your clay triangle on the ring and the crucible in the clay triangle for support. Gently heat the crucible without the lid to remove the water. Once the paper appears to be dry (after several minutes), heat the crucible more vigorously so that the filter paper begins to char (turning from white, to brown, to black) – but not so vigorously that the filter paper bursts into flame. If the filter paper bursts into flame you should cover it with the crucible lid to put out the flame, then reduce the amount of heat and remove the lid. Continue to heat moderately without the lid until all of the filter paper has turned black. 11. Once all the filter paper has turned black, vigorously heat the crucible without the lid in the hottest part of the Bunsen burner flame so that the bottom of the crucible is red hot. The charred filter paper (carbon) will gradually combust and be converted into CO2 gas. When the filter paper is entirely combusted only the white BaSO4 should remain in the crucible. Continue to heat the crucible vigorously until no charred filter paper remains. This should take about 10 minutes. 12. Allow the crucible to cool to room temperature. Weigh the crucible (without the lid) and its contents on the analytical balance. Record this mass on your lab report. 13. Place the crucible and its contents back in the clay triangle and heat vigorously (without the lid) for an additional 5 minutes. Then allow it to cool again and reweigh the crucible (without the lid) and its contents. If the mass is within 0.005 grams of your mass obtained in step 12, then record this mass on your lab report. If the mass has decreased by more than 0.005 grams, then either the BaSO4 is still wet or not all of the filter paper has combusted and you should repeat this step until you achieve a consistent mass. 14. Discard the BaSO4 in the proper waste container, then clean up as directed by your instructor. Gravimetric Analysis of an Unknown Sulfate Page 3 of 3 Santa Monica College Chemistry 11 Name: Date: Lab Section: Exercise: Graphical Analysis of Data using Microsoft Excel© Part 1: Simple Linear XY Plot • Using Excel, prepare a linear XY plot of “Pressure vs Temperature” with the supplied data as instructed. Print out a full-sized copy of this graph and staple it to this report. • Which data is plotted on the y-axis? the x-axis? • Record the following information from your graph: The equation of the fitted trendline The value of the slope of this line The value of the y-intercept of this line • Is the fit of the trendline to your data good (circle one)? Yes / No Explain your response. • Determine the temperature (in K) of the cooled neon gas when it has a pressure of 181 torr: a) via extrapolation and estimating b) via the equation of the trendline Show your calculation for (b) in the space below. Part 2: Two Data Sets and Overlay • Using Excel, prepare an XY plot of “Light Absorbance vs Amount of Red Dye” with the supplied data as instructed, showing two sets of linear data simultaneously. Print out a fullsized copy of this graph and staple it to this report. • Record the equations of the trendlines fitted to Dataset A: Dataset B: Graphical Analysis of Data using Microsoft Excel Page 1 of 2 Santa Monica College Chemistry 11 Part 2 Continued • Perform a “simultaneous equations calculation” to determine the x and y values for the point of intersection between these lines. Show your work in the space below. Part 3: Statistical Analysis and Scatter Plot • For the SMC dataset (only), determine the following values using Excel: Mean SO4-2 concentration • Median SO4-2 concentration Are there any outliers in the SMC dataset (circle one)? Standard deviation in dataset Yes / No If yes, which measurements are the outliers? Show the calculations you used to identify the outliers in the space below. Exclude the outliers and re-calculate the following values for the SMC dataset using Excel: Mean SO4-2 concentration Median SO4-2 concentration Standard deviation in dataset • Using Excel, prepare a Scatter Plot with the supplied data as instructed, showing both the SMC and ECC datasets simultaneously. Print out a full-sized copy of this graph and staple it to this report. • Which dataset (SMC or ECC): Shows more scatter (less reproducibility)? Has the larger standard deviation? Contains the more precise measurements? Graphical Analysis of Data using Microsoft Excel Page 2 of 2