1 - IUP



Fitting Lines to Data using Microsoft Excel 2007

1. Open Microsoft Excel 2007.

2. Enter your x values into one column, say Column A, and your y values into another column, say Column B.

3. Install the Analysis Toolpack, if necessary.

Click on the Data tab. If Data Analysis is not present in the Analysis group, then you have to install the Analysis Toolpack using the following steps. Click on the Microsoft Office button [pic][pic] . Then select Excel Options. A dialog box will pop up. Click Add-ins, and then in the Manage box, select Excel Add-ins. Click Go. In the Add-Ins available box, click on the Analysis ToolPak check box. Then click OK. If a dialog box appears indicating that Analysis ToolPak is not currently installed on your computer, click Yes to install it.

4. Click on the Data tab. Then in the Analysis group, click on Data Analysis. The Data Analysis dialog box will pop up.

5. Select Regression from the list of analysis tools. Click on the OK button.

[pic]

6. Fill in the Regression dialog box.

a. Click in the Input Y Range text box. Drag to select your y value data in the spreadsheet. In the example above, this would be B1 through B10, including the label, Height, in the first row. Excel will enter the values $B$1:$B$10 into the Y Range text box.

b. Click in the Input X Range text box. Drag to select your x value data in the spreadsheet. In the example above, this would be A1 through A10, including the label, Foot Length, in the first row. MS Excel will enter the values $A$1:$A$10 into the X Range text box.

c. Check the Labels checkbox, if the first row of your data columns contains names or labels for the columns.

d. Check the Line Fit Plots checkbox, if you want Excel to graph the line; otherwise, skip to Step 10.

e. Click the OK button.

[pic]

7. Click on the graph that appears. Excel will add a Chart Tools tab at the top of the window. Click on Chart Tools. Then click on Change Chart Type. A dialog box will appear.

8. Click on X Y (Scatter) in the left column and then click on the first pictogram in the X Y (Scatter) row. Click on the OK button. Excel will add the line to the graph of the data points.

[pic]

9. Add the trend line. Click on the chart. Click on Chart Tools at the top of the window. Click on the Format tab. Click on the Chart Area drop down menu. Select Series “Predicted Height”. The word, Height, is the column heading of your y values. Click on the Chart Tools Layout tab. In the Analysis group, click the Trendline drop down menu, and select Linear Trendline.

[pic]

10. The y-intercept and slope of the line appear at the end of the ANOVA section of the new sheet created by Excel, in the Coefficients column. The first row contains the y-intercept, in this case, 20.96229479. The second row contains the slope, in this case, 4.272054844. So, the equation for this line is

y = 4.272054844 x + 20.96229479

where x represents a person's foot length and y represents the person's height. You will learn the meaning of the rest of Excel's output when you take a statistics course.

[pic]

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

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

Google Online Preview   Download