|
|
Tutorial: Data Analysis with Excel |
|
|---|
In the previous two Excel tutorials, we have discovered two ways to obtain least-squares estimates of the slope and intercept of a best-fit line:
Both of these methods are quick and useful, but they are mere toys compared to the method outlined in this tutorial: using Excel's Analysis ToolPak add-in. Here is a screenshot of the calibration data (click to download the file):
First let's check that the add-in has been installed. Go to the Tools | Add-Ins sub-menu; you should see the following screen.
Make sure that the Analysis ToolPak is checked. Doing so will add a Data Analysis item to the Tools menu.
Select the Data Analysis item, and then scroll down and select Regression
A dialog box will pop up. Select the ranges of the X and Y data; you can click on the funny little icons to select these ranges. It is also often a good idea to look at one of the residuals plots.
After clicking 'OK' a sheet will be created with statistical information about the linear regression.
Click on the above image to download the Excel file. If you chose that particular option, a plot of the residuals will appear to help identify outliers and evaluate the appropriateness of the chosen regression model.
 |
Watch the sequence of events leading to these last screen-shots |
|
|
|