Close the pervious workbook
Click on File
Click on Close
When asked if you would like to save Click on No

 

Open a new workbook and enter the data as it appears below:
(Click on File then Click on New)

We now want to analyze the data in columns A and B.
From the menu:
Click on Tools then Click on Data Analysis
Scroll down and click on Regression
Note: if data analysis is not present, you will need to go to "add-ins" (under Tools) to add the "Analysis Tool pack". ( Repeatedly click on the down arrow to the right of the choices in the dialog box until regression appears)
Click OK
A new dialog box will appear:
Click inside the box next to Input Y range
Highlight cells B3-B12 (or type B3:B12)
Click inside the box next to Input X range
Highlight Cells A3-A12 (or type A3:A12)
Under the heading Residuals Click on Line Fit Plots
Click OK
(*Note: on older versions of Excel it may be necessary to also check the "Regressions" box in addition to the "Line Fit Plots" box in order to have the Predicted Y's output generated.)
Excel will open a new worksheet with information relevant to the regression and a plot.

We only want some of this information, so we need to copy it to sheet1:
On Sheet3 Highlight Cells A16-B18
Click on Edit
Click on Copy
Click on Sheet1 (tab at bottom of window)
Click in Cell A15
Click on Edit
Click on Paste
Return to Sheet3
Click on Sheet3
Copy Cells B24-B34
Click on Sheet1
Paste into cell C2
On Sheet1 you should now have the data you entered, the predicted Y values and:

The intercept value is the y-intercept
x variable 1 value is the slope

These are two important pieces of information that we need to add to our data list:
In the data column, add an X value of 0 (cell A13)
and a predicted y value of -0.24 (y-intercept) (cell C13)
(This will give us a best fit line that extends through the y-axis)

 

We now need to graph the information so we have data points and our best fit line (predicted y):
Highlight the cells A2 - C13
Hit the chart wizard button on the tool bar (it looks like a bar graph)
Click on Next >
Click on XY(Scatter)
Click on Next >
Click on Box 1
Click on Next >
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
Click on Box next to Value (Y)
type a y axis title
Click on Finish

 

We now need to make our best fit line a solid line with no squares: Double click on the data point that goes through 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 agian)
In the dialog box Under
1. Line
Click on Custom
Select weight and color of line
2. Marker
Click on None
Click OK

 

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 turn in, check assignment for additional information needed (see section 12).