One feature of Excel that is important to the chemistry laboratory is linear regression. We are going to use the Analysis Tools to find the slope, y-intercept, and predicted values of Y for data.
Your data for the density of water experiment should be similar to the one
We now want to
analyze the data in columns A and B.
From the menu:
Click on Tools then Click on Data Analysis Note:
If you do not see "Analysis" under Tools, then you will need to add in the
Analysis Tool Pack. Go to Tools, then Add-ins, then click on the box for Analysis Tool
Pack. After hitting "ok", you should be able to see "Analysis" under
the "Tools" menu. If not, then you did not install all of the options when you
installed Excel on to your computer. Thus, you will need to reinstall Excel and make sure
that you select the full installation option.
Scroll down and click on Regression
(Repeatedly click on the down arrow to the right of the choices in the dialog box
until regression appears)
A new dialog box will appear:
Click inside the box next to Input Y range
Highlight cells B5-B9 (or type B5:B9) or wherever your mass data is
located on the spreadsheet.
Click inside the box next to Input X range
Highlight Cells A5-A9 (or type A5:A9) or wherever your volume data is
located on the spreadsheet.
Under the heading Residuals Click on Line Fit Plots
Excel will open a new worksheet and input a information relevant to regression and include a plot.
We only want some of this information, so we need to copy it to sheet1:
predicted Y values (in the red box) are needed to generate the best line through
your data. Copy this column and paste it into the sheet that has your mass
and volume data. Paste it into the column right next to your mass of water
data. The slope of the line is the "X Variable"; it is in the
blue box. The Y intercept is the value of your line when x=0. So, on
sheet 1, put this value in the Predicted Y value column and put a zero in the
same row in the Volume column.
On Sheet1 you should now have
We now need to graph the information so we have data points and our best fit line
Highlight the cells that have your data (volume, mass, predicted Y)
Go to the top bar on your Excel file, and click on Insert, select
Chart. This will open up a box.
Click on XY(Scatter) The top box should be selected. See the graphing section for pictures.
Click on Next >
Click on Box under Chart Title
type a title for your plot
Click on Box next to Category (X)
type a X axis title (Volume (ml))
Click on Box next to Value (Y)
type a y axis title (Mass (g))
Click on Finish
We now need to make our best fit line a solid line with no squares:
Double click on the marker that is at x=0
(A dialog box should appear - if you do not see a dialog box click on Edit,
then Click on Undo - try to double click again)
In the dialog box Under
Click on Custom
Select weight and color of line
Click on None
Remove the gray background (see section 9)
You now have a plot with a best fit line.
Add your name and the date to your worksheet (to any cell on the sheet1)
You will print out both the sheet1 and the chart to put in your notebook. (see section 12).
Back to the Tutorial
This tutorial was written by Dr. Laura Maki, it was last updated for Excel2000 on March 2, 2004 by Koni Stone. email@example.com