Excel Slope/Intercept Instructions

Excel has functions which calculate the SLOPE and INTERCEPT of a regression line given a dataset. It also has a function (FORECAST) which creates predicted values. These instructions walk you through the steps needed to do the assignment for the regressions computer assignment. Similar steps will work to create predicted values for any dataset.

Overview

It is a good idea to set up your spreadsheet in some logical fashion. While it is possible to create a nice graph from data with the x-component data at the top of the worksheet and the y-component 1000 cells below, it's not a good way to set things up. Organization of the worksheet is an art, but any attempts are very helpful especially when you have to come back to a spreadsheet you created last year and update a report before your boss's meeting at noon. (And who knows when your boss will want to use your spreadsheet when you're gone.) In addition to logical arrangement of data, headings and comments are very helpful additions to any complex spreadsheet.

Here we set up two extra columns of data to produce straight lines that pass through the original data. One line is the SD-line and the other is the regression line. So, we will set up columns of data with x-values next to y-values. I suggest leaving some room at the top of the spreadsheet to put comments, calculated values like the average, and titles. The bottom is OK too, but harder for others to find if they borrow your spreadsheet.

Make a simple scatter plot

You have two columns of data! You can make a scatter plot.

Highlight the two data columns (with their labels at the top) and choose the Insert tab. Under the Scatter option, click the first option (the mouse over says "Scatter with only Markers").
Make sure your variables are on the correct axes.

Make the SD line data

Copy the data into the worksheet and move the columns (cut and paste) so that the variables are lined up next to each other.
Find the averages and standard deviations for each column and the correlation coefficient; place them in cells adjacent to labels so that you (and others) know what they are.
We now have all the information needed to create the SD-line. Put an appropriate label at the top of a new column and enter a formula in the first row of data which uses the SD-line formula to calculate the correct Y value for the X value of that row. (The equation for the SD-line is in your notes.) Each row corresponds to an X value and its related Y values. Remember to use dollar signs before the letter and number of any cells that shouldn't change when you copy from one row to the next. For example, the average and SDs are the same for each row. If they appear in cell D4, then use $D$4 in place of D4.
The SD-line of interest will be downward sloping if the correlation coefficient is negative. We used a plus/minus sign in class to represent this. Use the function SIGN($F$6) to get a plus or minus one depending on the sign of the cell F6.
Check that your formula gives the correct answer. Then copy the formula down the column to the other rows. Check to make sure that the correct cell names (like A10) are getting incremented while the ones for the average, SD, etc are not. Add more dollar signs if needed and copy again.

Make the regression line data

Now to add the column for the regression line: (Did you label it first?)
There are three ways to get the regression line.

You can enter the formula for the regression line just like you did for the SD-line. You have all the needed averages and SDs.
You can have Excel calculate the slope and intercept for you, putting them into special cells (don't forget to label them). Then use the formula for a line in each row of the column. The functions SLOPE and INTERCEPT give you the values you need. They each require two data ranges as inputs. One for the X values and one for the Y values.
You can use the FORECAST function. This function requires three inputs: the particular X value for which you want a regression result, all the Y values, and all the X values. Remember to put dollar signs in the data ranges that you don't want to change when you copy.

Whichever way you choose, copy the formula down the column and check that it incremented (or left alone) the cell ranges correctly.

Make a scatter plot with SD and regression lines

You now have four columns of data! You can make a scatter plot with lines for the SD-line and regression line.

Highlight the four data columns (with their labels at the top) and choose the Insert tab. Under the Scatter option, click the first option (the mouse over says "Scatter with only Markers").
With the scaterplot selected, you can click the Layout tab at the top of the screen. Click Trendline > Linear Trendline. Choose the column for your SD line. Repeat for your regression line.
You may have to change the scaling on the axes. Click on the x-axis to select it. Right click it and select "Format Axis..." You can change the minimum and maximum values here. Repeat for the y axis. You can zoom in on your scaterplot by adjusting the numbers.