How to add a trendline (and a few other things) using exp 8 as an example


First task is to enter in the time data. Simply type 0 into cell A2 and hit enter. In cell A3, type "=A2+15" without the quotation marks of course. Then, fill the data down as shown in the first tutorial until you have your total number of data points shown. In this example the final reading was taken at 645 seconds.


The second part is to enter the absorption values in. This is a bit more tedious as you need to enter these in by hand.


Now, choose an arbitrary out of the way cell, such as G1 like I have chosen, to type your Ainfinity value in.


Here we are going to use column C for the A-Ainfinity values. In C2, type "=B2-$G$1" without the quotes. The reason we use the $ is to set cell G1. Without the $, Excel would change the G value to G2, G3, and so on as you fill down, as it does for the B column values. We don't want this, as there is only one Ainfinity value. Once that has been entered in, fill down as far as you have data.


Your sheet should look something like this.


Now you need to take the natural log of the C column. In cell D2, type "=ln(C2)" without quotes, and fill down.


Now we need to highlight data for our graph. Click on cell A2. In the chart wizard this will be our x-axis data. We want time on the x-axis.


Drag the box down until you have the rest of the column highlighted. Important note - do not click on any other cells in your spreadsheet at this point... You still have another column to highlight.


Now, while holding the control button down, click on cell D2, or, whatever cell starts your data for ln(A-Ainfinity)... Don't let up yet.


Keeping ctrl depressed, drag the box down until you have all the data for ln(A-Ainfinity) highlighted. When you're done, you'll have both columns similar what you see here. Don't click anything else yet...


Just like in previous experiments, click on the chart wizard button at the top of your screen and choose "XY (scatter)" as the chart type. Since you already have your data selected, clicking "Next >" will bring you to this screen.


In the options window enter in your chart title and axis title information.


Then click on the other tabs. For gridlines uncheck all the boxes so no gridlines show up. For the legend, uncheck that box so the legend is no longer visible. You have one set of data here, you don't need a legend.


Clicking on next will give you your graph.


Right click in the middle of it and choose the format plot area option and make the background area white.


Now, right click on the y-axis and choose "Format Axis"


When you see that box show up, choose the "Scale" tab. Go down to the field for "x-axis crosses at." What you are doing here is telling the x-axis to cross somewhere other than zero, so you still have the x values at the bottom of the graph. In this example, the lower y-value is -2, so that is what I have entered. Now the x-axis will go through the y-axis at y=-2, and it will no longer be on the top of the graph.


Clicking "ok" will give you this.


Now, right click on one of your data points. Choose the "Add trendline" option.


For type, you want "Linear regression." This is the upper left hand box, and is the default line chosen. You shouldn't have to change this field.


So, then you want the "Options" tab. This will take you to this screen. Find the field for "display equation on chart" and check that box. This will display the y=mx+b equation on your graph related to the equation of the trendline.


This is your finished graph. Now you have the slope value to find kobs. Remember that kobs = -slope.