Regression

Linear Regression Line Using Excel

1. Input your data,. Then move you cursor to a new square.
[pic]

2. Go to the f(x)   function and pick Statistical,
[pic]

3. Click on the LINEST function, this will provide us with the slope of the line value or m. Note the form of the data is the y values first and then the x values. Select OK.
You can then click and drag on our y values, which in this example or located in column C and then move down and select the x values which are located in column B. [pic]

4. Select OK and the answer should appear in the box where you started. I have labeled this m in the next square for my own benefit.
[pic]

5. Next the y intercept values is needed, so move your cursor to a new blank space, such as right under the m value, select f(x) function as before and scroll down until you find the
Intercept function. Select OK.
. [pic]
6. You will be prompted to input the y values and then the x values just like before. Click and drag on these values to input them into the spread sheet equation.
[pic]
7. You see the results of the calculations above, but select OK and it will put the value into the spread sheet.
Here is the final results. Check these against the books values.
[pic]

There is an easier way already built into EXEL.   Do a scatter diagram on the data in columns 2 and 3. Once you have the scatter diagram, click on the finished graph, then go to the top tool bar and select Chart and select Trend Line. It will plot the trend line on your data. Under the option choice you can select to write equation on the graph.
[pic]

Finished product of the scatter diagram with the trend line and the linear regression equation.
-----------------------
X values

Y values

Stat. package

This will give us m or slope

Slope = .6811

Stat. package

Intercept