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²).
• Click OK.

The regression line displays among the data points and the regression equation and R² display underneath. You can select and re-position the equation and R² 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² 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.