Linear Data, Equations and Least Squares Regression

Overview

Introduces students to Microsoft Excel and how to use the application
to produce charts and linear regression lines. Students analyze the slope,
y-intercept and correlation coefficient of the lines.

Materials and Equipment

- Computer Lab
- Microsoft Excel
- File Storage (floppy disks or networked file server)

Learning Objectives

- Plot data using Excel
- Create a chart in Excel
- Interpret the positive slope of a line as a rate of change in the context of real-life data
- Interpret the y-intercept of a line in the context of real-life data
- Interpret the meaning of the correlation coefficient of the least squares regression line

Guiding Questions

- What is the slope of the line? How is the slope computed?
- What does the slope tell us about the direction of a line?
- What does the slope tell us about the steepness of a line?
- What do we know about the y-intercept of a graph?
- What is a least squares regression line? How is it calculated?
- What is a correlation coefficient (r)?
- What does the correlation coefficient tell us about the data (points being plotted) and the resulting least squares regression line? What is the range of r?

Lesson Outline

- Generate as a class the real-life data from the following question: "If you were traveling at 40 miles per hour, how far would you go in 1 hour? Two hours? Three? Four?"
- Have students enter the data into an Excel spreadsheet and generate a XY (Scatter) chart based on the data.
- Have students include a Trendline (use Linear Type), and display both the equation and determination coefficient (r²). Have students compute the correlation coefficient (r).
- Have students save their spreadsheet. Make sure it is properly labeled.
- Use the chart and data to facilitate answers to the Guiding Questions. Pay particular attention to the slope, y-intercept and correlation coefficient analysis.
- Have students gather data at home for Lesson Two: Down The Drain.

Assessment

- Homework or Quiz based on Guiding Questions
- Excel Spreadsheet (Spreadsheet Rubric)