Here’s a step-by-step tutorial for using MS Excel (a ...



Here’s a step-by-step tutorial for using MS Excel (a spreadsheet program) to generate graphs and determine lines of best fit, etc. Excel is available to all St.F.X users who have computer access.

For this brief tutorial, I’m going to use the data we looked at in Monday’s class. The reaction of butyl chloride (C4H9Cl) with water (page 579).

After opening the Excel program, you can enter the data into the program by clicking on one of the boxes/fields on the screen. I’m starting by entering the time data into the column labeled “A”. In cell “A1” (column A, row 1), I’ve typed the heading for the column (and the units that the data is given in, seconds). Check ‘er out:

[pic]

Now I’m going to enter the rest of the time data, more-or-less like you see in the table on page 579.

[pic]

We can do the same for the other data (concentration):

[pic]

Starting to look a little better now. A few basic problems though…first, the heading in column two doesn’t fit into the column (it extends into the next). This will cause problems if you enter another data column. To fix this, place the mouse cursor on the edge of the column label (that is, on the line between the column labeled “B” (blued out) and the one labeled “C.” Click and drag the column width to something that will allow you to see the entire heading. This is what it looks like in the middle of this procedure…

[pic]

Now, we can see the heading, but the numbers entered don’t show the fourth decimal in all cases, like they do in the data table. You can show this by editing the default cell/column settings. Do this by clicking on the label “B” – this will highlight the column:

[pic]

Now, from the menu above this spreadsheet, click “format” and then “cells”:

[pic]

This brings up a new window. When you open this window, you should see this:

[pic]

The “number” entry is highlighted by default, which is good, because this is what we want to change. By clicking on the “decimal places” field, we can change the number of decimals of our data. Change this to four:

[pic]

…and then click “ok.” Now you should have concentration data to four decimals:

[pic]

You can do all sorts of fix-er upper stuff in that window to change the appearance of your spreadsheet and its data.

Great, now if we want to plot this data, we can do it easily by using the “chart wizard” button (I’ve highlighted it in the picture below). This button will let you make a variety of plots. The kind we typically want to work with is just an X-Y plot. What we’ll do now is create a plot of concentration data vs. time data (the convention for this plot is Y vs. X, so this means concentration will be our Y-axis data and time will be our X-axis data).

[pic]

Click the chart wizard button and bring up this window:

[pic]

For the plot we want to create, choose the ”XY scatter” option and then click “next”

[pic]

We see a series of points that have been plotted already. This is not our plot.

[pic]

Click on the “series” tab at the top of the grey area in this window and then “remove” the series.

[pic]

This is what you should see now:

[pic]

Now, you need to tell Excel what data will be used in the plot. Click “add” to create a new data series for Excel. This should yield the following on your screen:

[pic]

Groovy…now you need to indicate the X and Y data for the plot. Time data is held in column A. This is our X data, so click in the “X values” field. I’ve shown the cursor in this field in the next picture. (If no series is listed already, click the “add button”).

[pic]

You can enter the X data by now left-clicking (hold this button in) on the spreadsheet cell A2 (i.e. the first time data cell) and drag the selection down to cell A11:

[pic]

After letting go of the mouse button, you should see this:

[pic]

The data in the “Y-values” field is not the data we want to enter, so delete this.

As it turns out, the data selected in the “Y values field” is the data range we want. This is not always the case, so it might be a good idea here to delete this and get some practice in entering data again. Click the “Y values” field, which will highlight it, and then the delete key on your keyboard.

[pic]

This gets rid of the data that was entered. Now, enter your Y-axis data by doing the same thing you did for the X-axis data; that is, left-click (and hold) the Excel spreadsheet at cell B2 and drag down to select the data shown:

[pic]

Next, you’ll see this:

[pic]

You can also name this series:

[pic]

Click “next.” You can enter the axis labels in this window:

[pic]

Click “next” again, select “as new sheet”, then “finish,” and Bob’s your uncle:

[pic]

If you want to do some fancy math using Excel, you can save yourself a whole lot of time, rather than using a calculator. Let’s say we want to plot ln[C4H9Cl] vs. time. To do this, we’d have to calculate the natural logarithm of each concentration and then plot the data. With Excel (and other spreadsheet programs), this is done in seconds.

Let’s say we want to create a third column, next to the concentration column, containing data which describes ln[C4H9Cl]. Go to the spreadsheet table and click the cell immediately to the right of the first concentration cell (C2):

[pic]

Now, you can calculate the ln (natural log) of the concentration given in cell B2 in more than one way, but the easiest way is probably the “function wizard,” highlighted in the following picture (the fx button, just above and to the left of the column C label)

[pic]

This will bring up another window in which you select a function which will operate on some other data.

[pic]

In the field of this window that says “most recently used”, click and select “all” – the ln function was in the window shown above already, but chances are, you probably haven’t used the ln function recently, so it likely will not be on your screen. By selecting the “all” entry, you will be able to find the natural logarithm function (alphabetically). Select it and then click “ok,” bringing up this window:

[pic]

We want to the ln of the concentration given in cell B2 to be entered in cell C2. Click the B2 cell to do this. You’re telling the program to calculate the ln of 0.1000 by doing this).

[pic]

Then click “ok,” et voila:

[pic]

Position the cursor at the lower right corner of the new cell. This should create a solid black cross. Left click and drag down to cell C11. This calculates the natural log of the concentrations from cells B2 to B11:

[pic]

We can get rid of the data in cell C11 (ln 0 is undefined). Label the column “ln[C4H9Cl]” so that you know what this data means. We can make a plot of ln[C4H9Cl] vs. time in much the same way we did before, but remembering that this will involve C2 to C11 data for the y-axis, and A2 to A11 data for the X-axis. This will yield:

[pic]

I’ve taken out the gridlines and played around with the font of the chart to make it look a little better. If we wanted to calculate a linear line of best fit for this data, we would do the following: right click on any of the data points and select “add trendline,” shown as follows:

[pic]

Select “linear regression” and then click the “options” tab:

[pic]

This will yield the following:

[pic]

To get the equation of this line, check the “Display equation on chart” selection. By checking the “Display R-squared value on chart” box, you can determine just how linear the data is. An R2 value of one means a perfectly linear data set. This will be useful in some kinetics concentration-time problems, in determining the order of reactions. Checking both of these boxes and clicking “ok” should yield this:

[pic]

Left click and drag the information at the lower left of the line to somewhere else on the plot (i.e. where it can be read more easily). This is the equation of the line (Y=mX + b format) and the R2 value. Then right click on this information (the equation of the line and the R2 value), bringing up a menu and select “format data labels.” You can change the font (text size, etc.) from this window.

[pic]

And there you have it, a linear regression, which yields the slope of the regression (which will yield the rate constant, k, for this reaction). Using the equation

[pic]

which has the format Y = mX + b, you can also see that the Y-intercept will yield the original concentration of C4H9Cl.

This information will be particularly useful for plotting titration curves in the pH Titration lab (which you’ll do soon). It will save you a lot of time doing the calculations by Excel rather than by calculator.

Brian

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download