Data Analysis lab 1 - This is the lab manual for the first lab in CHEM112 PDF

Preview

Summary

This is the lab manual for the first lab in CHEM112...

Description

Analysis of Lab Data This week's lab focuses on using Excel1 to analyze data and create graphs. Graphing is an important part of working with your lab data. Often, trends in your data cannot be seen until it is graphed. This lab is in 3 parts namely: Basic Charting, Chemical Applications and Regression analysis. When followed sequentially, you will be able to understand the importance of data analysis as a scientist. In this lab, you will create several sheets in the Excel workbook. Each sheet will be for one section of the lab. In addition to naming the sheet in the Sheet tab, also please make sure to add a line at the top of the sheet with your name, and the title of the section. This is because at the end, when you print the sheets to a pdf file, the Sheet tab does not get printed.

1. Basic Charting This section of the lab goes through the introductory exercises on graphing with Excel. 1.1 The Rough Plot Start Excel and start a new worksheet. At the bottom of the page, double click on the tab to rename ‘Sheet 1’ to be ‘Graph 1 - Rough Plot.’ (also on the first row of the sheet, type your name and the same title). • Into cell A6 of your worksheet, enter the value 0.0 and into cell A7 enter the value 0.2. • Highlight A6 and A7, then drag the fill handle(That is, the small dot on the lower left corner the selected cell(s)) down to cell A16 to enter the rest of the data.(By doing this you will form a sequence starting from 0 to 2). • At cell B6, type =A6^1.5. (This is a formula that raised the value in cell A6 to the power 1.5. All formulas start with an equal sign.) • Repeat the fill process described above from cell B6 to cell B16. This copies the formula into the other cells and adjusts the number A6 to always represent the same relatively located cell as in the original formula. Excel automatically calculates the other cells for you. • Highlight from cell A6 to B16 and click Insert from the Menu bar.

1

All Queen’s Students have free access to the complete MS Office suite of programs, Word, Excel, Powerpoint, OneDrive, OneNote, etc. You can find the information and download the apps to your device using the Search button on the top left of the Queen’s Home page.

•

At the chart section, click on Scatter (X,Y) charts icon . From the drop down, choose the scatter option. If done accurately, a chart corresponding to the highlighted data is plotted. Drag the new chart to an empty place below the data on your spreadsheet for to make printing easier later.

1.2 Editing a Chart • To start editing your chart, click ‘Chart Title’ and rename it with ‘Chem 112 Rough Plot’ •

1.3

Click anywhere on the chart and select the element icon beside the chart. Check the ‘Axis titles’ box and rename the two axis with y-axis and x-Axis respectively. o What happens when you uncheck the Gridlines box? Record your observation under the chart. o What happens when you select Data Labels? Record your observation.

Multiple Plot Chart

To begin this exercise, start a new worksheet by clicking the new sheet icon at the bottom of the page. Rename this new sheet “Graph 2” • In this exercise, you will plot the last three columns against the first column in one chart with three data series. • Copy the data from Table 1 (below) to your new worksheet. • Highlight all the data to produce a chart using the same steps you used to produce the Rough Plot above. • Edit your chart using the steps in 1.2 above. Don’t put the data labels on this time though. Table 1: •

1

50000

20000

1

2

100000

300000

32

3

150000

500000

243

4

200000

800000

1024

5

250000

1000000

3125

6

300000

1300000

7776

7

350000

1500000

16807

8

400000

1800000

32768

9

450000

1900000

59049

10

500000

1900000

100000

•

11

550000

1700000

161051

12

600000

1400000

248832

13

650000

1200000

371293

14

700000

800000

537824

15

750000

500000

759375

16

800000

300000

1048576

17

850000

150000

1419857

18

900000

100000

1889568

19

950000

100000

2476099

20

1000000

1000

3200000

To take this further, you will edit the 3 series so that one is indicated by a line and no marker, one by markers but no line and one by markers and a line. (Hint: double-click a series (i.e. a data point on the graph in the series). click on fill and line icon under Format Data Series. Alter the settings to suit the goal stated above). Drag this chart to a place below the data again, so you can print it easier later.

2. Chemical Applications This section will include concepts more relevant to chemistry and its applications. Open a new worksheet 2.1 Fractional Composition of a Diprotic System Your first example involves an application directed towards analytical chemistry. Specifically, you will look at the fractional composition of a diprotic acid system. That is, the composition of a diprotic acid over the pH range 0 to 7. To refresh your memory, a diprotic acid is an acid that can donate two protons. For example, H2SO4 is a diprotic acid. We will be examining the fumaric acid system. Fumaric acid is an organic acid with the formula C4H4O4. The dissociation constants for Fumaric Acid are K1 =8.85×10-4 and K2 =3.21×10-5. • To begin this exercise, create a new worksheet and Label it Fractional composition • and into cells A1, A2, and A3 enter your name, student number and title(Fractional Composition of a Diprotic System) respectively. (You can widen a column to fit by double clicking on the line separating the two columns on the top of the spreadsheet, between the column letters) • Into cell A4 and A6 type 'K1 =' and 'K2 =' respectively. • Into cells A5 and A7 type 8.85E-4 and 3.21E-5 respectively and into cells B4 and B6 type pK1 and pK2 respectively. (Note, 8.85E-4 in a computer is the same as 8.85×10-4 in regular text.) • Into cell B5 type the formula =-log10(A5). o Entering this formula should give a result of 3.053057 in cell B5.

Adjust to two decimal places by clicking the decrease decimal icon in the ribbon until you have only two decimal places showing. Then you will have the value 3.05 in the cell • Into cell B7 type the formula =log10(A7) and adjust to two decimal places as stated above. • Into cell C4, type in the heading pH and enter the pH data in the sequence below into column C (i.e. in cells C5 to C40). 0.0, 0.2, 0.4, 0.6,…………………………………………, 7.0 o It is easiest to enter the data as in the previous example, starting at 0, finishing at 7 and increasing in steps of 0.2 pH units. • The next thing that we want to do is to convert the pH units into proton concentration, [H+]. You should remember that the relationship between these two is: [H+] = 10^-pH Remember that the ^ (circumflex) symbol means "to raise to the power of". • Into cell D4 type the heading [H+]. • Into cell D5 type the formula =10^-C5. • Using the fill process, fill in the [H+] formula so that all of the pH values are converted into [H+] (Excel will automatically calculate the [H+] down the column). •

• •

Now that we have [H+], we can use it to find , , and . These are the fractional compositions of the three species of the polyprotic acid. Into cells E4, F4, and G4 type Alpha H2A, Alpha HA- and Alpha A2- respectively. The appropriate formulae2 are:

........1

........2

........3 • •

2

Enter the appropriate formulas. o For example, into cell E5 type the formula =D5^2/(D5^2+D5*A5+A5*A7) In the formula, we have constants and variable. To calculate automatically down the column, follow the steps below. o At the formula bar directly above the worksheet, place your cursor in-between A and 5, press the F4 key on your computer. Place the cursor in-between A and 7 also and press F4 key. o This will keep the value in cell A5 and A7 constant throughout the column.

These formulae represent the fractional composition of all the species of a polyprotic acid in water. You will learn more about this in second semester. For now, just go with it.

Then highlight cell E5 to E40 and excel will automatically do the calculations down the column. Enter the formula for Alpha HA- and Alpha A2- into cell F5 and G5 respectively. Use the formula above as a model to figure out the rest. Change the references A5 and A7 from relative references (which will change as you drag and copy) to fixed references by putting dollar signs in front of the column and or row index. Thus, make sure in your formula any reference to A5 should be changed to $A$5, etc. Now select the three cells (E5-G5) and copy them down to the bottom of the table as described above to automatically fill each column. Make sure you don’t change the references to cells in column D to fixed references. They should remain as relative references so they will flow properly as you copy the columns. o If you have entered the formulas correctly, you should get the numbers 9.99E-01, 8.84E-04, and 2.84E-08 in cells E5, F5, and G5 respectively. o

•

Now the data set is complete, and we can produce a chart of fractional composition (yaxis) versus pH (x-axis) from the data. Don’t plot the [H+] column. In our chart, we want to use the pH data for the x-axis and the fractional composition data for our y-axis. Using the knowledge you have acquired on how to produce charts, you will make a chart similar to the previous one but use only lines this time on your scatter chart Select this icon •

to do this quickly when you’re inserting your chart. This should give three lines, one for each species. Title the graph, label the axes and move it to below the data.

3. Linear Regression In this section you will learn to do a linear regression and plot the resulting line on a chart. 3.1 Beer's Law According to Beer's Law the relationship between concentration and absorbance is linear (A = ebc, where A = absorbance, e = molar absorptivity, c = concentration and b = path length in cm). For the following data we will assume that b = 1. Does the data satisfy Beer's Law (i.e., is the y intercept zero) and what is the molar absorptivity? To answer these questions, we will do a linear regression on the data using the Data provided. We will also plot the data and draw the linear regression line on the chart. 1. Create a new worksheet called Beers Law. Plot the following data with concentration on the x-axis and absorbance on the y-axis: Concentration (M): 40, 46, 52, 58, 64, 70, 76, 82, 88, 94 Absorbance: 0.227, 0.256, 0.286, 0.316, 0.345, 0.37, 0.399, 0.425, 0.445, 0.46 Be sure to Title the chart and label x and y axes. Indicate the series with markers, but no line. 2. To perform a linear regression on the data •

Click on the chart element icon

beside your chart.

•

Click the arrow in front of Trendline and then select ‘More Options…’. A task pane called Format Trendline appears o o

•

Under trendline options choose the linear icon . Scroll down the task pane and check the ‘Display Equation on Chart’ and ‘Display R-squared value on chart’ boxes, respectively. This will display the regression line equation and R-square value on your chart. You can drag the equation so you can see it clearly. Drag the chart so it’s below your data.

Answer the questions below the chart directly on the spread sheet. You may need to look up a few things. o What is the R squared (is the linear fit a good one?)? o What is the slope of the line (i.e. e)? o What is the y intercept and the error3 in the y intercept (is Beer's law completely satisfied)? o What is the error in the slope?

3. Finally we want to compare the linear regression described above with a polynomial regression (That is, a regression of second order and above). To perform a Polynomial regression on the data • •

Click on the chart element icon beside your chart. Click the arrow in front of Trendline and then select ‘More Options…’. A task pane called Format Trendline appears o o o

•

Under trendline options choose the polynomial icon . Scroll down the task pane. Under forecast, type 40 into the backward option check the ‘Display Equation on Chart’ and ‘Display R-squared value on chart’ boxes, respectively. This will display the regression line equation and R-square value on your chart. You can drag the equation so you can see it clearly.

Question o Does the data agree with the previous linear regression (in step 2)?

3.2 Full Regression Analysis.

3

Hint. This method didn’t calculate an error for you so you have to make an educated guess based on the way the graph looks.

Let’s try a regression again, this time using the data analysis capabilities of the Excel spreadsheet. To do a thorough analysis of data, you need to install the Analysis ToolPak4 which is one of the Excel Add Ins. If you are using Excel 2010, Excel 2013, Excel 2016, and 2019, the same step highlighted below applies: •

Click the File menu item, click Options, and from the Excel options shown. click on Add-Ins.

•

Various Add-ins are displayed, select Analysis ToolPak and then click Go.

•

A box pops up on the screen, select Analysis ToolPak by checking the box, and then click OK.

Note OK, so now, you have the Analysis Toolpak activated. Let’s do the actual analysis. First, open a new worksheet, name it Regression Analysis and type the data below into two columns

Now, you are ready to do the data analysis. Insert the “Scatter” type graph and carry out linear regression on the data just like you did earlier. By doing so, you will be able to find the slope, intercept and R2 values. Move the plot to below the data for printing later.

4

If you are using a Mac or other version of Excel the Analysis Toolpack may not have been installed on your computer, Kindly use this link- https://bit.ly/34YqUl6 to get to the Microsoft web site so you can download it for free.

Parameter R2 is often used to determine if the line fit is a good one. Unfortunately, R2 is relatively useless except as a general indicator of whether the fit is good. What you need is the uncertainty of the slope and the uncertainty of the intercept and R2 doesn’t give that. The Analysis toolpak in Excel will give you those results. So, to do the full analysis, follow the steps below: •

Click on Data tab from the menu and click on Data Analysis from the Analysis section.

•

From the list of analysis tools, scroll down, select Regression, and click Ok

•

Then input the x range, the y range. To do this, highlight all the cells representing x-axis and Excel will input the range for you. Do the same for y-axis.

•

Under Output Options, select Output range and then click on a cell below your current work. This will be where the results of your analysis gets placed

•

Under Residuals, check the Residual Plot5 and the Line Fit Plots6 box and then click Ok. This will tell Excel to draw two new graphs and put them on your spreadsheet along with the other results

Look now at the results of the analysis printed where you specified. You will see therein, several versions of the R function you are familiar with. However, the part of the spreadsheet that you are interested in is where you find the details of the parameters and errors of the equation you used (slope and intercept in this case).

Intercept X Variable 1

Coefficients -1.60015 0.852702

Standard Error 4.117104026 0.132003491

Now, you see the parameters of your fit, labeled “intercept” and “X Variable 1” (a.k.a., slope). Their values and their standard errors are clearly printed out for you to use. The standard error is given to more digits than you need. Use the first digit as an indicator of the number of significant digits to include in your results. Thus, you would report the slope as 1.1 + 0.2 or 1.1(2) and the intercept as 4 + 4 or just 4(4). Note that the intercept is very uncertain. The value of the number is the same magnitude ad the error in the number. If we knew in advance that the intercept should be zero then we can redo the data analysis selecting the option “Constant is zero” to obtain the following results If this plot is one where you know in advance that the data MUST go through (0,0) you should redo the regression analysis. This time, when you select

5

Residuals plots are useful to see that any residuals (the difference between your data and the fitted line) are randomly distributed about zero. Non-random residuals (moving away from zero, on average) would indicate that you are using the wrong kind of equation to fit the data. 6

Redrawing the line fit plots here will give you essentially the same thing as the first plot you drew in this unit but I like to include it just to visually see that I did everything correctly in the regression analysis.

your x and y ranges, include the heading of the column in your selection and click the Labels check box. Also, click the Constant is zero box to force the intercept to be zero. Your results will now include these labels on the graphs and printed results.

Select a spot at the bottom of your worksheet for the output and rerun the analysis. Now, your results should look like this.

Intercept V/mL

Coefficients 0 0.805131

Standard Error #N/A 0.043873227

Notice that the error in the slope (now labeled V/mL in the spreadsheet) is smaller. That is because we forced one extra point, the (0,0) point to be in the graph. Creating your final report for submission When you have completed all these modules, Print the entire workbook to a pdf document. When you select print (or press control-p), under Settings select “Print entire workbook”.

Make sure that all the graphs and data are on the printout. You can visually inspect the pages in the preview pages to the right. Go back to the work sheet and Adjust the positioning of things if some of the graphs are off the printed page. You will see some dotted lines on the spreadsheet to indicate to you where the edges of the printed pages will lie. You should have about 9 pages (more or less) of material in your pdf file. Then submit your pdf file using the link found for Lab 1 in the Assignments tab of the onQ site for your course.

Your first lab is complete! You have now learned to use excel to do simple data analysis. Along the way, you have learned to use many features of the spreadsheet program. Many of these are common to all spreadsheet apps so this knowledge will set you up for success no matter what programs you use in the future....