# Regression in Excel

• Correlation

• Insert your data into an Excel spreadsheet. (See Accessing Excel data from the computer lab) Insert a row at the top and add titles to the columns if necessary or desired.

• Click on Tools -- Data Analysis. Select Correlation. Click OK.

• Position the cursor in the text box next to Input Range and use the mouse to select the columns containing your data. Check the box next to Labels if appropriate. Click the radio button next to Output Range and specify a cell in the upper right corner of an empty block of cells for the output. Click OK. Excel will output a table containing 4 cells. The value of r is the number in the first row, second column.

• Coefficients for the Least Squares Regression Line

• Insert your data into an Excel spreadsheet. (See Accessing Excel data from the computer lab) Insert a row at the top and add titles to the columns if necessary or desired.

• Click on Tools -- Data Analysis. Select Regression. Click OK.

• Position the cursor in the text box next to Input Y Range and use the mouse to select the cells containing the values of the response variable. Follow the same procedure to select the cells for the explanatory variable. Check the box next to Labels if appropriate. Click the radio button next to Output Range and specify a cell in the upper right corner of an empty block of cells for the output. (You may want to check the boxes for Residuals and Residual plots at this time if you will need that information.) Click OK.

• Excel outputs several tables. The first contains the values of r and r2 (among other things we don't need yet). The first column of the last two rows of the output contain the coefficients of the least-squares reqression line. The first number is the intercept, the second is the slope.

• Adding the Least-Squares Regression Line to a Scatterplot

• Insert your data into a spreadsheet and create a scatterplot (see Scatterplots).

• Click on the scatterplot to select it.