There are actually two ways to do a linear regression analysis using
Excel. The first is done using the **Tools** menu, and results
in a tabular output that contains the relevant information. The second
is done if data have been graphed and you wish to plot the regression line
on the graph. In this version you have the choice of also having
the equation for the line and/or the value of R squared included on the
graph.

**1) Using the Tools menu version of the regression analysis
to obtain the results of the analysis in a table.**

In order to do this version of the linear regression analysis, using
Excel, you have to begin by creating a data table that has the independent
and dependent variables. This table has to have the data in columns,
not rows, in order for the regression to work properly. A sample
data table is shown below. (If you have created a table in rows,
not columns, it is easy to transform it into a columnar table. Copy
the table and then do a **Paste Special** to a new location. In
the **Paste Special** menu, select **Transpose **(and **Paste Values
**if
the table is made by cells with formulas) and the new table that will be
created will convert the rows into columns. A table created this
way is shown below.

To do the linear regression, go to the **Tools** Menu and select
**Data
Analysis**. From the **Data Analysis** window select
**Regression**.
That will open a wizard that will look like the picture below:

The next step is to tell the Regression Wizard the things it needs to
know; the location of the Y data, the location of the X data, and
the place to put the result of the regression analysis. In the example
shown the Y range would be the column of RTs beginning with 667 and ending
with 1210. The X range would be the column beginning with 0 degrees
and ending with 180 degrees. Each of these can be filled in by putting
the cursor in the window you want to fill in and then clicking on the top
of the column and dragging to the bottom, holding the left button down.
That will copy the cell references into the blank. Move the cursor
into the next blank to be filled and repeat. In the **Output Options**
section, you have the opportunity either to have the result of the regression
analysis put on a new, blank page in your Excel workbook, or to be located
on the same page as the data. To locate the result on the same page
as the data, click in the button labeled **Output range**, and then
click in the box to the right of that button to move your cursor there.
Now, click on a cell that you want to be the upper left hand corner of
the output and that cell location will be put into the wizard. Click
the OK button and the result of the regression analysis will be located
in the spot that you have chosen.

Highlighted in the sample output shown above are the value of R Square (.81), the Y Intercept value (574 msec) and the slope, which is identified as the X Variable 1 (2.88 msec/degree). These are the only parts of the output that will be relevant to the things that we will do in this course.

**2) Doing a linear regression analysis where the results will
be displayed as a line on a graph with the option of also including the
equation for the line and/or the value of R squared.**

To begin, create a graph of the data. Because linear regression
implies that both the X and Y variables have at least interval or ratio
properties, the appropriate chart type to select from the menu when creating
the chart is an **XY Scatterplot**. A sample of a graph of the
data shown above, created this way, appears below.

Now, select the chart so that the "handles" are visible, the small squares
at each of the corners and the midpoints of each of the lines that form
the box around the graph. That will cause the **Chart** menu to
become available at the top of the page. From the **Chart** menu
select Add Trendline (because a linear regression is one kind of curve...trendline...that
Excel can fit to a set of data). That will produce a menu that looks
like the one shown below.

In the menu the box labeled Linear has been selected. That will
cause a straight line to be fit to the data. If you click on the
**Options**
tab in the top of the window, you will have another set of choices, which
are shown in the next picture.

I have checked the boxes to **Display equation on chart** and to
**Display
r-squared value on chart**. If you want neither of these, or only
one, to appear on the chart, click on the boxes to add or remove check
marks as desired. Below is a picture of the graph that results when
both boxes have been checked.

Note that the line has been drawn, it has been added to the Legend,
and the equation and R squared have been printed on the graph. The
equation and R squared can be dragged to other locations in the graph,
their fonts formatted differently, etc. Working with them is just
like working with other objects in the chart. Note that the values
for the slope, Y intercept, and R squared are the same as were obtained
using the first form of the analysis.