Linear relationships

From WikiEducator
Jump to: navigation, search

Description of activity and Excel instructions:Linear relationships

Dataset: animals.xls

Create a scatterplot of gestation vs. longevity

Here's a quick reminder on what to do:

  • Select columns B and C (gestation and longevity).
  • 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 gestation period and longevity is linear and positive. Now let's compute the correlation between gestation period and longevity.

Compute the correlation of gestation and longevity

  • Click in a cell outside of the first three columns of data.
  • Type "Correlation"
  • Move to the adjacent cell on the right.
  • Use the function wizard to create a correlation between gestation and longevity, function=CORREL.

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

Remove the outlier (elephant) from the correlation

  • Scroll down to row 16 of the data. (This row contains the values of the variables for the elephant.)
  • Click on the row header 16 to select the entire row of data.
  • Right-click on the 16 and choose Delete Rows.

The elephant data is removed from the correlation and the chart. To directly compare with and without the elephant included, use the Undo and Redo buttons to compare the before and after correlations and scatterplots.

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