# Linear relationships, calculate & plot regression line

Description of activity and Excel instructions: Linear relationships

Dataset: olympics.xls

## Create a scatterplot of year vs. time

Here's a quick reminder on what to do:

- Select columns A and B (year and time).
- Select
**Insert**>**Chart...**.

The *Chart Wizard* displays.

In *1. Chart Type*:

- Select
**XY (Scatter)**. - Select the
**Points Only**version. - Click
**Next>>**.

In *2. Data Range*:

- Click '
*Data series in columns*. - Check
**First row as label**.

No changes are needed for *3. Data Series*.

- Click
**Next>>**.

In *4. Chart Elements*:

- Enter a title for the chart.
- Enter a title for the X axis (explanatory variable).
- Enter a title for the Y axis (response variable).
- Decide if you want to display a grid in the chart background.
- Decide whether you want to display a legend.
- Click
**Finish**.

Observe that the relationship between the 1500 meter race's winning time and the year is linear. The least squares regression line is therefore an appropriate way to summarize the relationship and examine the change in winning times over the course of the last century. We will now find the least squares regression line and plot it on a scatterplot.

## Plot the least squares regression line

- In edit mode on the graph, right-click one of the points on the graph.
- Select
**Insert Trend Line...**.

The *Trend Line* dialog displays.

- Click on the
**Type**tab. - Verify that
**Linear**is selected under*Regression Type*. - Check the
**Show equation**box. - Check the
**Show correlation coefficient (R**.^{2}) - Click
**OK**.

The regression line displays among the data points and the regression equation and R^{2} display underneath. You can select and re-position the equation and R^{2} value, if they are displayed on top of the data points.

Return to the OLI's Linear relationships page to complete the first interpretive exercise.

## Remove the outlier (1896 winning time) from the plot and calculations

- Observe that row 2 contains the 1896 winning time
- Click on the row header
**2**to select the entire row of data. - Right-click on the
**2**and choose**Delete Rows**.

The 1896 data is removed from the chart. The regression equation and R^{2} value have changed. To directly compare with and without the 1896 winning time included, use the *Undo* and *Redo* buttons to compare the before and after regression lines.

Return to the OLI's Linear relationships page to complete the second interpretive exercise.