CH110 ASSIGNMENT:  To Excel in Chemistry Assignment Instructions:  Hand in the fill-in-the-blanks sheets along with any supporting evidence such as required plots and table of values

Subject:ChemistryPrice:10.99 Bought8

Share With

CH110 ASSIGNMENT:  To Excel in Chemistry

Assignment Instructions:  Hand in the fill-in-the-blanks sheets along with any supporting evidence such as required plots and table of values.  All work must be completed in pen (not pencil) or typed (preferred) directly in this document.  No other lab report component is required for this assignment.  Note that 15 marks out of 100 are assigned for including units and the correct number of significant digits so double check your work before handing in.   

DUE DATE: Tuesday November 10^th, 11:59PM to the online MyLS CH110 lab dropbox as ONE DOCUMENT/submission.

Google Sheets CANNOT be used to complete this assignment. Be sure to download the free version of Microsoft Office available to all WLU students. 

OBJECTIVE

To manipulate chemical data in a graphing program and to perform linear regression and data analysis.   To learn techniques to write proper computer generated scientific lab reports.

Course Learning Outcome

? Demonstrate competency in linear graphical data analysis

 

Introduction

 

In chemistry, is it often useful to determine the mathematical relationship between two physical parameters.  Graphs are useful tools that can help discover and elucidate such relationships. First, plotting a graph provides a visual image of data and any trends therein.  Second, analysis of the graph can provide a mathematical expression that describes the relationship between the two physical parameters.  Third, graphs can be used to estimate the values of the physical parameters that are beyond the range of the data set and that are between observed data points; the former process is called extrapolation while the latter is called interpolation.

One common relationship between physical parameters is a linear relationship.  A linear relationship occurs when two or more quantities are proportional to one another.  When a linear relationship exists between physical parameters and those parameters are graphed, a distinct pattern is observed—the plotted points resemble points on a straight line.  A straight line can be fitted to the plotted points. Historically, fitting a line to plotted points was done by hand and with a straight edge but today modern software is used for this purpose.  Software uses a statistical technique known as linear regression to create a straight line that is the best fit between all the data points on a plot. In addition, software often provides a mathematical expression of the line, usually in the familiar slope-intercept form: y = mx + b.  This equation expresses the mathematical relationship between the two variables plotted, and allows extrapolation and interpolation.  Figure 1 shows the general form for a straight line graph.

 

Slope-intercept form of best-fit line:

y = mx + b

Δy

y-intercept:  b

Δx          Slope:  m = Δy/Δx =  y₂ − y₁/x₂ − x₁

 

y

a

x

i

s

best-fit line

b

x- axis

 

Figure 1:  The general form of a graph displaying graph components.

Computer spreadsheets are powerful tools for manipulating and graphing quantitative data.  In this exercise, the spreadsheet program Microsoft Excel^? will be used for this purpose.  In particular, students will learn to use Excel in order to explore a number of linear graphical relationships.

Apple Computer Users:  These instructions were written to apply to Microsoft Excel PC.  There may be some difficulty in applying these instructions if an Apple computer system is used. However, the procedures for Excel for Mac should be similar to those provided here.  There are two options: Complete the assignment using your Apple computer or use a school computer that has Microsoft Excel installed.    

Part 1: Simple Linear Plot

Scenario:  A gas laws experiment is designed to measure the volume of 1 mole of helium gas at a variety of different temperatures, while keeping the gas pressure constant at 758 Torr.  The data that was collected is given below in table 1.

Table 1: Temperature and Volume of Helium data collected in a gas laws experiment.

Temperature (K)	Volume of Helium (L)
203	14.3
243	17.2
283	23.1
323	25.9
363	31.5

1. Launch the Excel program.  Go to the Start button, located at the bottom left on the screen, then click Programs, followed by Microsoft Excel or click on your Excel icon on the desktop screen.  If a new workbook does not open automatically, click “File”, then “New” and then “Blank Workbook”.
2. Enter the above data into the first two columns (A and B) in the spreadsheet.
   • Ensure the first row contains the column labels.
   • One way to plot an xy graph is begun by placing the data in columns so that the columns are in a specified order.  The x values must be entered to the left of the y values in the spreadsheet.  Remember that the independent variable, the one that you, as the experimenter, have control of, is plotted along the x- axis while the dependent variable, the measured data, is plotted along the y-axis.
   • Highlight column A by clicking on the column letter heading.  Select column A and B by holding down the left mouse button and dragging to column B.  With both columns selected, move the cursor to the border that lies between column A and B until the cursor becomes a vertical line with arrows on either side.   Double clicking will auto-fit the contents of both columns so that the headings are clearly visible. Clicking on any cell will deselect the columns.  Alternatively, the “Format” button on the “Home” tab can be used to set column widths.
3. Highlight the set of data but not the column labels that you wish to plot (Figure 2).

Figure 2:  Highlighted data set of Temperature and Volume of Helium

Click on the “Insert” tab at the top left, followed by “Scatter” button (Figure 3).

 

Figure 3

:

Highlighting the Insert Tab which displays the Scatter button

.

 

Choose the scatter graph that shows data points only.  This is the option labeled “Scatter with Only Markers” (Figure 4).

 

Figure 4:  Scatter with Only Marks button

You should now see a scatter plot on your Excel screen, which provides a preview of your graph (Figure 5).

Figure 5: Preview of Excel graph with data from table 1.

If all looks well, it is time to add titles and label the axes of your graph. 

• Click inside the chart. “Chart Tools”, which is a group of three tabs (Design, Layout, Format), will appear.
• Click on the “Layout” tab, located under “Chart Tools”.
• Click on “Chart Title” then “Above Chart” to add a title.  The default title, which is automatically added by Excel, is “Chart Title”.  This is never an appropriate title for a scientific graph.  The title of a graph should be given a meaningful, explanatory title so that when presented with your graph the reader should be able to determine what is plotted quickly and easily.   The title should start with “y versus x”, where y is the dependent variable and x is the independent variable and followed by a description of your system. For example, the title “Volume versus Pressure for One Mole of Air at 298 Kelvin” describes a plot of volume, on the y-axis, and pressure, on the x- axis, for one mole of air at a constant temperature of 298 Kelvin. 

To change the default title to the title you have chosen, click inside the text box to place the cursor in the box then delete the default title and add your title.

• Click on “Axis Titles”, which is located to the right of the “Chart Title” button.  Select “Primary Horizontal Axis Title” and then select “Title Below Axis” to enter the horizontal axis label.  Click on “Primary Vertical Axis Title” and then “Rotated Title” to add the vertical axis label.  The axis labels must include the physical measurement (volume, temperature, time, etc.) and the units of measurement.  Figure 6 shows the

 

location of the Chart Title and Axis Title buttons on the Layout tab.

Figure 6

:

Chart Title and Axis Title buttons needed to create chart and axis titles.

 

4. The next step is to fit a straight line to these plotted data points.  In Excel a line that is fit to data is called a “trendline”.  A trendline represents the best possible fit of a straight line to your data.  To do this you first need to "activate" the data points.  Do this by clicking on any one of the data points.  When you do this, all the data points will appear highlighted. 

Now click on the “Trendline” button which is located in the “Layout” tab under “Chart Tools”.  Choose “More Trendline Options”.  The “Format Trendline” window should now appear (Figure 7).

The radio button for the “Linear” trendline, under “Trend/Regression Type” should be selected, it is usually the default trendline option, but if it is not, select it. 

Now select the “Display Equation on Chart” box and the “Display R-squared value on

Chart” box by clicking in the check-box to the left of these options.  Then click the

 

“Close” button.

Figure 7

:

Format trendline window displayed in Excel spreadsheet.

 

5. The equation that now appears on your graph is the equation of the fitted trendline and appears in the slope-intercept form of a straight line.  The R² value, known as the coefficient of determination or goodness of fit parameter, provides a measure of how well the equation fits the data.  The closer the R² value is to 1, the better the fit.  Generally, R² values of 0.95 or higher are considered good fits.   The program will always fit a trendline to the data no matter how well or how poor the straight line fits the data.  You must judge the quality of the fit and the suitability of this type of fit to your data set.  One way of doing this is to use the R² value.
6. In our plot there are no data points in the region of 0 – 150 on the x- axis, see Figure 6.  To better present the plot, we need to remove this unnecessary area from the plot.  We do this by adjusting the x-axis scale.  Click on any one of the numbers on the x- axis.  Then, click “Axes” under the Chart Tools Layout tab.  Select “Primary Horizontal Axis” and then “More Primary Horizontal Axis Options” from the dropdown menu.  This will open the “Format Axis” window.  Ensure “Axis Options” is selected (Figure 8).  Select the “Fixed” radio button which is located to the right of the “Minimum” option.  This will activate the box located to the right of the radio button. Type 150.0 in the box.  This sets the minimum x- axis value to 150.  Press Enter.  The Format Axis window will close and the x- axis of your plot should now show a range of 150 to  400.

 

 

Figure 8

:

Axis options window open to change minimum x-axis value.

 

7. To remove the legend from the right hand side of the graph, click on the legend box and press “delete” on the keyboard.   Alternatively, select the “Layout” tab and click on “Legend”. Select “None”.
8. Print out a full size copy of this prepared graph and staple it to your report.  Ensure your graph is already selected, then you should only have to choose “Print” to obtain a full-size printout.  Then record the following information on your report:
   • the equation of the best-fit trendline to your data
   • the slope of the trendline
   • the y- intercept of the trendline
   • whether the fit of the line to the data is good or poor, and why.
1. Graphing the five data points of the gas law experiment allows us to recognize a relationship between gas volume and temperature.  The graph contains a visual and mathematical representation of that relationship.  The graph can now be used to make predictions of gas volume and temperature.  For example, suppose the 1 mole sample of helium gas is cooled until its volume is measured to be 10.5 L and you are asked to determine the gas temperature.  The volume 10.5 L falls outside the range of the plotted data.  How can you find the temperature if it doesn't fall between known points?  There are two ways to do this.

Method (1): Graphical Extrapolation.

Graphical extrapolation uses the graph and the best-fit-line to estimate the coordinates of a point that is beyond the range of the data set. This is accomplished by extending the best-fit-line and measuring on the graph the coordinates of the desired point.

• Click on the “Layout” tab along the top menu, then on “Trendline” and then “More Trendline Options”.  The “Format Trendline” window will open (Figure 9).
• In the section labeled “Forecast” you want to enter a number in the box labeled “Backward”. In Excel, the backward direction is to the left of the trendline.  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 area where the volume is 10.5 L.  In the Forecast box you will see the word “periods” to the right of the forward and backward boxes.  The “periods” term refers to “units of”, so in this case, one period equals one degree Kelvin.   Enter the correct number of Kelvins required to go back past 10.5 L. Click “Close” and the line on your graph should now be extended in the backward direction.

 

Figure 9

:

Displays the backward forecast box in the format trendline window.

 

• Now use your graph to estimate the x value by hand-drawing a straight line down from y = 10.5 L to the x-axis using a ruler and pen.  Record this value on your report.

Method (2): Extrapolation via Equation of the line.

This method is largely a mathematical method, only the equation of the best-fit line is needed.

• Enter the value for volume into the equation of the trendline and solve for the unknown temperature.
• Show how you solved for temperature and record your answer on your report.
• In general, using the equation to extrapolate is more precise than graphical extrapolation.

 

Part 2: Using Functions

 

Scenario:  A second gas laws experiment is designed to measure how quickly helium gas diffuses from a source to a detector ten meters away.  The temperature of the sourced is varied and the time of diffusion is measured.  The data collected from the experiment are given in Table 2.

Table 2:  Temperature and Time of Diffusion Data for Helium Gas.

Temperature (K)	Time of Diffusion (ms)
2.00 × 102	9.72
3.00 × 102	7.93
4.00 × 102	6.87
5.00 × 102	6.15
6.00 × 102	5.61
7.00 × 102	5.19
8.00 × 102	4.86

1. Obtain a new worksheet in Excel by clicking on the Sheet 2 tab at the bottom left of the window.  Enter the data from Table 2 into columns A and B.  To enter the temperature values in scientific notation, you must first format the cells in which the values are to be entered. 
   • Select the cells you want to format.
   • On the “Home” tab select “Number” to open the “Format Cells” window.
   • Click on the “Number” tab
   • On the “Number” tab, click Scientific in the Category list.
   • In the “Decimal places” box, enter the number of decimal places that you want to display. We want two decimal places in this case.  We use scientific notation for the temperature values so we can retain three significant digits. 
   • Click “OK”
   • Enter the temperature values in the cells without scientific notation, for example 2.00×10² is entered as 200.

Create a plot of time versus temperature. Follow the procedure as outlined in Part 1. Include the R² value.  Answer these questions in your lab report: Why is time plotted on the y-axis?  Which set of data is placed on the left in the Excel worksheet?

2. You will make three additional graphs involving this data. For each plot you will convert the temperature values into temperature related values and then plot these against time of

diffusion.  You will convert each temperature value in Table 2 into

1. 1. the natural logarithm of the temperature value,
   2. the square root of the temperature value and
   3. the inverse of the square root of the temperature value.

You will instruct Excel to fit a trendline and calculate the R² value for each of our plots.  You will compare all four graphs and use the R² values to determine which plot gives the most linear relationship between the two variables.

3. Using functions to manipulate data values in Excel is quite straightforward.  As an example, follow the procedure below to obtain a column of  the natural logarithm of the temperature values:
   • To enter the natural logarithm of the first temperature value, follow these instructions: Select cell C2.  Type "=LN(A2)", without quotes.  Press enter.  Alternatively, you can type “=LN()” then put the cursor between the parentheses and click on cell A2 and press enter.  The equals symbol “=” indicates to Excel that you are entering a function. The “LN(A2)” function instructs Excel to calculate the natural logarithm of the value in cell A2, the first temperature value.  The natural logarithm of the value in cell A2 should now appear in cell C2.    It is always a good idea to check that Excel is making the correct calculation.  Use your calculator to calculate the natural logarithm of the value in cell A2 and compare it to the value in cell C2.
   • To quickly create the formulas for the rest of the cells in column C, you will use the fill handle option.  Click on cell C2, the cell that contains the natural logarithm of cell A2. A fill handle symbol will appear at the bottom right of the cell.  Figure 10 shows an example of a fill handle symbol.  Place the cursor over the fill handle.  The cursor will change from a white cross to a black cross. If your cursor appears as a black cross with arrows, reposition it over the fill handle until you see a black cross.  Hold the left mouse button down and drag down column C until you are in the same row as the last temperature value.  This should be cell C8. 

When you release the mouse button, all the cells will fill with the natural logarithm of each corresponding temperature value.  This is a quick and easy method to copy the same function into many cells but at the same time changing the reference cell.  The reference cell is the cell the formula uses for its input information in its calculation.  The reference cell for cell C2 is cell A2. Notice how Excel copies the same formula from cell C2 to cell C3 but it changes the reference cell from A2 to A3. This is called a relative copy and continues down the column if you use the fill handle method.  Don’t forget to check a few of the values in column C to ensure that Excel is making the correct calculations.

Figure 10:  An example of a fill handle on a selected cell.

• • The newly calculated data will likely have up to 10 digits recorded in the cells.   This is not the correct number of significant digits.  Each original temperature value contains three significant digits.  The result of a logarithm has the same number of significant digits to the right of the decimal as there are significant digits in the number whose logarithm is being calculated.  Thus, the logarithm of an original temperature value will have 3 significant digits to the right of the decimal place. To change the number of decimal points, highlight the data in cells C2 to C8.  Click the “Home” tab then click on the decrease decimal icon (Figure 11) until the correct number of significant digits is present. 

 

Figure 11: Location of the decrease decimal icon in the Home tab.

• • Now we want to graph the natural logarithm of the temperature versus the time of diffusion, fit the plot with a trendline and have excel calculate the R² value, but the time of diffusion values, which are in Column B, are on the left-hand-side of the natural logarithm of temperature values, which are in column C, and if we are use the same graphing method that we learned in Part 1, we need the time of diffusion values on the right-hand-side of the  natural logarithm of temperature values.

To remedy this, we can copy the time of diffusion values from column B and paste them into column D. To do this, highlight B2 to B8 by click and dragging the mouse, then select “Copy” and then select cell D2.  Select “Paste”.  This will paste the time of diffusion data into column D.  Create a natural logarithm of the temperature versus the time of diffusion  with a best-fit-line and R² value.

4. Repeat this procedure for the other two functions of temperature.  In the case of the square root of the temperature, there are a couple of ways to enter the formula.  First, Excel has a native or built-in function to calculate the square root of a value in a cell. The formula is “=SQRT(A2)”, where SQRT() is the built-in function and A2 is the reference cell.  The function will return the square root of the value in cell A2. 

Another method uses the power function. The formula to be entered is “=POWER(A2,0.5)”, where POWER() is the formula, A2 is the reference cell and 0.5 is the power to which the reference is raised.  Another form of the power formula is “=A2^0.5”, where the circumflex character ( ^) means “raised to the power of”. 

For the formula to calculate the inverse of the square root of the temperature there are a number of formulas: “=1/SQRT(A2)”,  “=POWER(A2,-0.5)”,  “=POWER(A2,−0.5)”, =( A2)^(−0.5).  Once you have all the columns finished, complete the plots, fit a trendline and R² values to the plots and determine which is the most linear.

5. Record on your report:
   • the R² value for each of the four graphs
   • Answer the questions: Which plot produces the most linear relationship? How do you know?
   • the equation of the trendline for the most linear plot. ? the slope of the trendline for the most linear plot.

e)         Include all graphs along with the table of values that was used to create all four graphs .

Include this in your assignment as part of one document

For reference:  To print just the data and not the graphs, highlight all the data including titles and select “File” then “Print”.  From the drop down menu under settings that currently says “Print Active Sheets”, click on the down arrow and select “Print Selection”.   Then only the highlighted data should be printed.

Part 3: Two Data Sets with Overlay

Scenario: In a Beer’s Law experiment, a spectrophotometer is used to measure the amount of light absorbed for two different coloured nanoprisms.  The two sets of data collected are

 

presented in the table below.

Table 3: Concentration and Absorbance values for two Nanoprisms A and B.

Nanoprism A

Nanoprism B

Concentration A

(

mol/L

)

Absorbance

(

unitless

)

Concentration B

mol/L

)

(

Absorbance

unitless

)

(

0.100

0.049

0.800

0.125

0.200

0.168

0.850

0.285

0.300

0.261

0.900

0.440

0.400

0.360

0.950

0.620

0.500

0.470

0.600

0.590

0.700

0.700

0.750

0.750

 

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.  Superimposing plots is a viable option for presenting multiple sets of graphical data if the values of the dependent variables of the two sets of data are similar and if the values of the dependent variables of the two sets of data are similar.

1. Enter this new data on a new spreadsheet (Sheet 3) in Excel. Label your data columns with appropriate titles.  Again, remember to enter the x values to the left of the y values.  Use the increase/decrease decimal icon to show all significant digits for the data.
2. First, plot Nanoprism A information as an xy- scatter plot.  Fit a trendline to this data using linear regression and obtain the equation of this line.
3. Now add Nanoprism B information to this graph.
   • Activate the graph by clicking on one of the plotted data points.
   • Click on the “Design” tab and click on the “Select Data” icon.  The “Select Data Source” window should appear (Figure 12).

Figure 12: Select Data Source window displaying for Sheet 3.

• • Click the “Add” tab (Figure 12), and type “Nanoprism B” for the Series Name (Figure 13).
  • Click the collapse dialogue button, located just below and to the right of the “Series X values:” (Figure 13), This will minimize the edit series dialogue box and change the collapse dialogue button to a expand dialogue button. 
  • Select the values of concentration for nanoprism B (column C) with a click and drag motion of your mouse. Expand the Edit Series dialogue box by clicking on the expand dialogue button.
  • Select the collapse dialogue button for the “Series Y values” and select the values of absorbance measurements for nanoprism B. Expand the dialogue box by clicking on the expand dialogue button. Click on the “OK” button.  This will return you to the “Data Source” window. Clicking OK will close this window, plot the data for nanoprism B on the same graph as nanoprism A and return you to the main spreadsheet window.  

 

Figure 13

:

Edit Series window displayed for Nanoprism B Series.

Series

Y

values

collapse

dialogue

button

Series

X

values

collapse

dialogue

button

 

3. • Click on the “Layout” tab, then “Legend” and choose to show the Legend at the right hand side instead of on the graph if it isn’t already there.  To rename “Series 1”, click on the Legend box.   Right click and then choose “Select Data”.  Highlight “Series1” and then click on “Edit”.  The “Edit Series” window will appear.  In the Series Name box type “Nanoprism A”.    Click “OK”.  Click “OK” again.   The legend should be updated to Nanoprisms A. 
   • Fit a trendline to the data for nanoprisms B.
   • We want to remove two text boxes in the legend, namely, “Linear (Nanoprisms A)” and “Linear (Nanoprisms B)”. Click on the legend, then click one of the textboxes, press “delete” on the keyboard. Repeat these actions for the other textbox.  
   • Add an appropriate title and axis labels. Print this graph and attach to the report.  
4. Record the following information on your report:
   • the equation of the best-fit trendline for Nanoprism A.
   • the equation of the best-fit trendline for Nanoprism B.
   • attach a copy of your graph (ensure large enough to see points, line, etc.) Ensure you have proper significant digits in this equation of the line.

Part 4: Choosing the Correct Parameters for Graphing

Scenario: The following data was collected from an experiment which measures the rate constant (k)  of a first order hydrolysis reaction as a function of temperature.

Table 4: Temperature and Rate constant data for a first order hydrolysis reaction.

Temperature (K)	Rate Constant, k (s−1)
2.80×102	4.70 × 10−2
2.85×102	6.87 × 10−2
2.90×102	9.85 × 10−2
2.95×102	1.41 × 10−1
3.00×102	1.97 × 10−1

a)   The rate constant, k, and the temperature, T, are related via equation (1):

−Ea

	k=AeRT The parameter A is called the frequency factor, the units of which are identical to those of k (s−1 in this case), Ea is the energy of activation (units of kJ/mol), and R is the thermodynamic gas constant (8.31 x 10-3 kJ/K mol).  Since the relationship between k and T is an exponential one, the data set, when plotted, will not be linear.  Your goal is to mathematically transform Equation 1 from an exponential equation into a linear equation.	(1)
b)	How do you take an exponential function and transform it into a linear function?  Well, you have to transform the exponential term into a linear term.  To do this, simply take the natural logarithm of both sides of the equation and simplify.  This gives a new, equation (2) below. E lnk ln A           a

 

 

= − RT It is this function that you must plot to get a straight line.  But exactly what are you going to plot on the x axis and y axis to create this linear graph?  If you rearrange the previous equation, it might become evident (Equation 3).  Equation 3 is in the slope-intercept form of a straight line.  Identify y, x, m and b in Equation 3 and record them on your report.  E lnk       a 1        ln A

(2)

=− ₍ )+

R T      (3)

3. Enter the data from Table 4 into columns A and B on a blank spreadsheet (Sheet 4) in Excel. 
4. Is temperature part of the y-value or x-value in equation 3? Write a formula using Excel functions  to convert the temperature data from table 4 into values that will give a linear relationship as outlined in equation (3) and question (b).  Be sure your new values have

the correct number of significant digits.

5. Is the rate constant part of the y-value or x-value in equation 3? Write a formula using Excel functions to convert the rate constant data from table 4 into numbers that will give a linear relationship as outlined in equation (3) and question (b).  Be sure your new values have the correct number of significant digits.
6. Plot these new calculated values to obtain a linear plot.  Fit a trendline to your data set and display the equation of the trendline and the R² value with the correct number of significant figures.  The number format for the equation of the trendline is likely in ”general” format and will not have the correct number of significant figures. In order to change this, double click on the box containing the equation for the trendline and R² value.  The “Format Trendline Label” window should appear.  Click on “number” and then select the “Scientific” category with 2 decimal places. Select “close”. 
7. Record the equation of the trendline on your report with correct significant digits.  Then use information obtained from this equation to calculate:

? the value of E_a in kJ/mol. ? the value of A, in s^-1. 

Record and show your work with correct final number of significant digits for the calculations on the report.  Include the table of the new calculated data and the actual plot in your assignment.

Part 5: Statistical Analysis

Scenario: A sample of water was analysed for sulfate ion (SO₄^-2)  concentration.  Ten separate analyses were conducted.  The results of the analyses are presented in Table 5.

Table 5:  Sulfate ion concentration in ppm for water samples in lab #1 at WLU.

Analysis #	1	2	3	4	5	6	7	8	9	10
WLU Lab#1 (ppm)	35.9	43.2	33. 5	35.1	32.8	37.6	31.9	36. 6	35.0	32.0

Simple statistical analyses of this data set include determinations of the mean and median concentration, and the standard deviation of the data set.  Both the mean and median are measures of central tendency of the data, commonly referred to as the average.  Standard deviation is a measure of the amount of deviation or variation in the data set.  Standard deviation is a measure of precision. A small standard deviation indicates a data set that tends to group closely together, while a large standard deviation indicates a data set that is spread out over a large range.

The mean or arithmetic mean ( ¯^x ) is defined as the sum (?) of each of the measurements (x_i)  in a data set divided by the number of measurements (N)  (equation  4):

∑x ⁱ

¯x=

N The median (M)  is the midpoint value of a numerically ordered data set, where half of the measurements are above the median and half are below.  The median location of N measurements (equation 5) can be found using:

(4)

M=(N+1)/2 When N is an odd number, the 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.  Note that if there were only 6 numbers in the set (1, 3, 4, 5, 8, 9), the median location is (6 + 1) / 2, or the 3.5th value.  In this case the median is half-way between the 3rd and 4th values in the ordered distribution, or 4.5. Standard deviation (s)  is a measure of the variation in a data set, and is defined as the square root of the sum of squares divided by the number of measurements minus one (equation 6):

(5)

s

(6)

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 the standard deviation, the greater the variation in the data and the lower the precision in the measurements.

We use significant arithmetic (significant figures rules) as a way to maintain significant digits when determining standard deviation. The standard deviation and the average should both end at the same decimal place.  For example, if we had determined that the average mark on a class test, marked to unit precision, was 72.36598 with a standard deviation of 7.22014, we would express the class result as 72 (7), where 72 is the average, the standard deviation is reported in parenthesis, and both are reported to the same number of decimals.

 While the mean, median and standard deviation can be calculated with a calculator, it is often more convenient to use a computer to determine these values.  Microsoft Excel is particularly well suited for such statistical analyses, especially on large data sets.

1. Enter the data acquired by the students from WLU #1 into a single column of cells, for example cells A2 through A11 (cell A1 holds the title “WLU#1”), on a new spreadsheet in Excel (sheet 5).  Be sure to format the cells to provide one decimal place.  Then in an empty cell, usually directly below the data cells, you will instruct the program to perform the required functions on the data.

 To compute the mean:

• • Click on an empty cell below or to the right of the numbers for which you want to determine the mean.
  • On the Home tab, in the Editing group, click the arrow next to “∑ AutoSum”, a dropdown menu will appear, click “Average”, and then press ENTER.  Alternatively, type "=AVERAGE(A2:A11)" and press ENTER .

To compute the median:

• • Click on an empty cell below or to the right of the numbers for which you want to determine the mean.
  • Type "=MEDIAN(A2:A11)" and press ENTER .
  • Be sure the calculation does not include the value for the mean.

To compute the standard deviation:

1. • Click on an empty cell below or to the right of the numbers for which you want to determine the mean.
   • Type "=STDEV(A2:A11)" and press ENTER .
   • Be sure the calculation does not include the value for the mean or median.
2. Record on your report:
   • The Excel calculated mean, median and standard deviation for the WLU#1 data set with the correct number of significant digits.

Rejecting Outliers

Do all the measurements in the WLU #1 data set look equally good to you, or are there any points that do not seem to fit with the others?  If so, is it appropriate 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, for example you incorrectly read an instrument or incorrectly prepared a solution, you may reject the point without hesitation.  If, however, none of these errors are 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.

3. Using the criteria supplied above, determine if any measurements in the WLU #1 data set are outliers.
   • Record on your report which measurement(s), if any, are outliers.  Explain.
   • Then excluding the outlier(s), re-calculate the mean, median and standard deviation of this data set using Excel.

f)     Did the standard deviation change between the original and re-calculated data?  If so, what does this represent for your recalculated data?  Answer on the report sheet. 

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 or on your data sheet:

• that you did reject a point
• which point you rejected ? why you rejected it

Failure to disclose this could constitute scientific fraud.

Adapted from:  Scholefield, Michelle. “Using Excel for Graphical Analysis of Data”.

Chemistry 11 Lab Experiments.  Santa Monica College. CA. USA,  2007.

Name:                                                                   Date Submitted:                               ID#:_________________________________     Lab Section:                                    

ASSIGNMENT 1 – To Excel in Chemistry

 Instructions   :

Fill in the blanks using a PEN OR TYPED answers and submit all sheets and required graphs/tables only in ONE document to be uploaded to your online CH110 Lab MyLS dropbox.  No other lab report criteria is required.

 

Part 1: Simple Linear Plot

 

?	Which set of data is plotted on the y-axis?
	the x-axis?
?	Record the following information:
	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 a good fit? (circle one)     Yes / No Briefly explain your response:

                                                                                                                                        

                                                                                                                                       

                                                                                                                                       

                                                                                                                                        

                                                                                                                                                                                                                               

• Determine the temperature, in Kelvin, of the gas in the cold room when it has a measured volume of 10.5 L using:
  1. Extrapolating by graphical means                                                                                    
  2. Extrapolating by using the equation of the trendline                                                 

                                

Show your calculations for b) below:

• Include a printout of your graph to this report.  Be sure that your axes are properly labeled, with units, the equation of the line and the R² value are visible, and that your graph has an appropriate title.

 

Part 2: Using Functions

 

• For the Time of Diffusion versus Temperature plot, why is time plotted on the y- axis?
• For the Time of Diffusion versus Temperature plot, which set of data is placed on the left in the Excel worksheet?
• What are the R² values for the four graphs?  Fill-in the table below.

	Time vs. Temperature	Time vs. ln(Temp)	Time vs. (Temp)0.5	Time vs. (Temp)−0.5
R2

• Which plot of the four graphs produces the most linear relationship?  How do you know?
• Record the following information:

The equation of the trendline for the most linear plot                                                           

     

The value of the slope of this line                                                                        

• Include a printout of your graphs to this report.  Be sure that your axes are properly labeled, with units, the equation of the line and the R² value are visible, and that your graph has an appropriate title.
• Print the table of values that was used to create all four plots.  Include units and significant digits as appropriate.

Part 3: Two Data Sets and Overlay

• Record the equations of the trendline fitted to the plot of:

Nanoprism A:                                                                         Nanoprism B:                                                                        

• Include a printout of your graph to this report.  Be sure that your axes and title are properly labeled, with units, the equation of the line, the R² value and the updated legend are visible.

Part 4: Choosing Correct Parameters for Graphing

• Consider this equation which relates the rate constant (k)  of a hydrolysis reaction to temperature (T):

E ^{a 1}

lnk=− ₍ )+ln A

R T

If this equation is in the slope-intercept form of a line, which term in this equation corresponds to:

y?                                                         x?                                                         m?                                                        

b?                                                        

• Use appropriate functions to manipulate the data in Table 4 and create a linear plot.  Record the equation of the trendline below. Attach your table of values for the calculated data and your linear plot in your report.

Equation:                           _______                      

• Using the equation of the best-fit line determine a) and b) (see below).  Show your complete work in the space provided.
  1. the value of E_a (in kJ/mol)
  2. the value of A (in s^-1)

 

Part 5: Statistical Analysis

 

• For the WLU#1 data set, record the following values with units (determined using Excel):

the mean SO₄^2- concentration                                                             the median SO₄^2- concentration                                                                      the standard deviation in the data set                                                            

• Are there any outliers in the WLU#1 data set (circle one)?     Yes / No

If yes, which measurement(s) are the outliers?                                                                     

Show the calculations you used to identify the outlier(s).  If you determined that there were no outliers, explain how you came to this conclusion.

• Re-calculate the following values (using Excel) excluding the outlier(s) if appropriate:

the mean SO₄^-2 concentration                                                             the median SO₄^-2 concentration                                                                      the standard deviation in the data set                                                            

• Did the standard deviation change between the original and re-calculated data?  If so, what does this represent for your data?