Teacher: Ferne Byer

School: Mainland Regional High School

Title of Lesson: A Correlation and Regression Analysis on Data Sets – Using MS

EXCEL to Find a ‘Line of Best Fit’ on Linear and Parabolic Graphs.

Subject: Mathematics

Category 4: Instruction suitable for Grades 9-12

Dates That Lesson Was Conducted: November 4,5,8-10,15-19, 1999

Goals and Objectives of Lesson: By obtaining linear and parabolic data through both

physical experimentation and searches on the Internet, students will be able to

perform a correlation and regression analysis on data sets using MS EXCEL to

find a "line of best fit."

New Jersey Core Curriculum Content Standards addressed: Subject Area-

Mathematics; Standard #4.5; Cumulative Progress Indicators #1-8.

Day #1: "Fitting a Line to Data"- A Linear Model (see Appendix 1(a))

Day #2: "Fitting a Quadratic Model to Data" (see Appendix 1(b))

Day#3: "Spreadsheets"- An Introduction (see Appendix 2 (a)-(c))

Day #4: Classroom Demo using MS EXCEL – Computerized Spreadsheet

Applications: Calculating Weekly Payroll Earnings and Discounted

Prices (see Appendix 3(a)&(b))

Day #5: "Bouncing Ball Experiment" (see appendix 4(a)&(b))

Day #6: "Internet Searching Strategies"-(see Appendix 5(a)&(b))

Day #7: "Correlation and Regression Analysis"-Classroom DemoUsing MS EXCEL to Find a Line of Best Fit On Linear and Parabolic Graphs

Days #8&9: Computer Lab- Hands-On Activity for Graphing Data Obtained

during days #5&6 (see Appendix 6(d))

Day #10: Closure and Student Feedback & Assessment: class discussion,

student’s "summary essay" and "computer project" assignments.

• Hardware-enough PCs (with internet access) to accommodate no more than two students per computer terminal; printer
• Peripheral Devices – computer overhead cart with wall screen to perform

• Software – Microsoft EXCEL
• Bouncing Ball Experiment Materials –

• Student Handouts and Other Supplemental Materials –

1. Data Charts
2. Instructions

5 b-1: CPTA data sets, descriptive list

5 b-2: CPTA data sets, brief table list

5 b-3: U.S. postal rates data set (text, EXCEL file, plot)

5 b-4: Skid length- car accident data set (text, EXCEL

file, plot)

vi.) Appendix 6: (a) "Correlation & Regression Using EXCEL"

© "Step-by-Step Instructions for Graphing & Finding

a ‘line of best fit’ Using MS EXCEL"

vii.) Appendix 7: Additional Comments and Suggestions: see

"Section 3.1: Linear Function Graphing Lab" and

"Section 3.1a: Quadratic Function Graphing Lab"—

offers other computerized graphing lesson ideas.

Days #1&2: Teach students how to find a "line of best fit" using both a linear and

quadratic model. See the suggested lesson format outlined in

Appendix 1(a) & (b). Explain to students that they will be comparing

the paper-&-pencil methods learned during these first two days to

that of using a computerized spreadsheet program to obtain a "line

of best fit" during days #8&9.

Day #3: Show students how paper spreadsheet journals were kept by companies

before computerized spreadsheet programs were made available to them.

See suggested lesson format comparing the old and new methods in

Appendix 2(a). Also included are Lesson Masters 4-4(A)&(B) for use as

guided practice &/or homework problems.

Day #4: Give an overview of computerized spreadsheets and some basic

Day #5: Bouncing Ball Experiment

Objective: Students will be able to (1) obtain real-life data through

experimentation, and (2) use the methods learned during the first

two days of this instructional unit to (a) plot the data, (b) find a

"line of best fit" on both linear and parabolic (quadratic) scatter-

plots, and © qualify the data through calculations of slope, y-

intercept, correlation coefficients, and regression analysis

(R-squared values).

Materials:

Per student: pencil, notebook paper, graph paper, ruler

Per group: masking tape, tennis ball, stopwatch (should be easily

obtained from the Physical-Education Department)

Activity:

1. Break up class into small groups (4-5 students per group).
2. Bring the groups of students somewhere that has a large amount of wall space, preferably in a hallway, to conduct the

experiment.

3. Each group should vertically place a strip of masking tape from

the bottom of the floor up to a height of 6 feet. Using a ruler,

the group needs to mark off with a pencil units of measurement no greater than a ½ inch on the tape (i.e., draw

markings for 0 in., ½ in., 1 in., 1 ½ in.,…72 in.).

4. The experiment includes two (2) activities:

HEIGHT OF DROP (in.)	HEIGHT OF BOUNCE (in.)
72
60
48
36
24
12
0

If the group’s measurements are accurate (The teacher needs to

stress to the students the importance of the accuracy in their

measurements at the beginning of the experiment.), the above

scatterplot should resemble a linear model. It is important for the

teacher to instruct each group member to complete a chart/graph

similar to the ones above, since he/she will individually be using

and comparing this data on days #8&9 in the computer lab. (In the

lab, each student will be creating his/her own computerized

spreadsheet containing the same data in chart and graph form

using MS EXCEL – see "Details of Activity" for days #8 & 9.

Activity #2: Have students set up a data chart as shown

below:

By dropping a tennis ball from various heights, as specified in the

above chart, students will use a stopwatch to determine the

amount of time (in seconds) it takes for the ball to stop bouncing

completely. The group will then transfer their data from the table

onto graph paper, where they will individually construct a

graphical representation of their results. Refer to the sample graph

below; notice that in contrast to the linear graph above, this graph

will have an X-axis labeled "Time of Bounce," a Y-axis labeled

"Height of Drop," and a scatterplot that resembles a quadratic

(parabolic) model.

Closure: At the close of the class period, the teacher should collect both

the charts and graphs from all of the students and redistribute it

back to them on day #8 in the computer lab. At this time, students

should be instructed to complete a homework assignment that

requires them to calculate a slope, y-intercept, and correlation

coefficient (for linear model only) and an equation of the "line of

best fit" for both models, as was outlined for them during day’s

#1 & 2.

Day #6: Hold a class discussion on "Internet Searching Strategies." See the

suggested lesson format outlined in Appendix 5(a). Next, take the class

to the computer lab where they can connect to and explore the website

(http://192.154.43.167/green/math/cpta/data/index.htm) that contains

various "real-life" linear and parabolic data sets that they can choose

from for completing their computer projects that will be assigned to them

1. Choose MS EXCEL 2000 in menu; On a new spreadsheet:
1. In A1, type "Height of Drop."
2. In B1, type "Height of Bounce."
3. Fill in your data from the charts you completed on day #5:

Height of Drop	Height of Bounce
72	34
60	32
48	24
36	19
24	11
12	5
0	0

4. In D2, type "CORREL." Then in E2, type in the formula:

" =CORREL(a2:a8,b2:b8)." Hit ENTER.

5. In D3, type "SLOPE." Then in E3, type in the formula:

"=SLOPE(a2:a8,b2:b8)." Hit ENTER.

6. In D4, type "INTERCEPT." Then in E4, type in the formula:

"=INTERCEPT(a2:a8,b2:b8)." Hit ENTER.

(NOTE: The slope, y-intercept, and correlation coefficient calculations are

required only for the linear data set.)

2. To graph data:
1. Highlight all data in cells A1 to B8. Then click on the chart

symbol on the toolbar on top of the spreadsheet.

2. Choose "SCATTER PLOT" as type of graph and highlight the first picture under "Chart Sub-Type." Hit NEXT.
3. Follow the instructions for the "Title" and the "Labels for

Axes" by clicking on NEXT (Title - "Bouncing Ball Exper-

iment"; X-AXIS - "Height of Drop"; Y-AXIS - "Height of

Bounce"). When done, click on FINISH.

3. To add a "trendline" to a data series:
1. Click on the data series to which you want to add a trendline.

To do this, you must click on a data point in the graph; this

will cause "CHART" to appear at the top of the toolbar.

2. In the chart menu, scroll down to "ADD TRENDLINE" and

click on it.

3. On the "TYPE" tab, click on the type of regression trendline

that you want. (If you select "POLYNOMIAL," enter in the

order box the highest power for the independent variable. Thus, for Chart #1, highlight "LINEAR"; for Chart #2, highlight "POLYNOMIAL" for a parabola and enter 2 in the order box.)

4. Next, click on "OPTIONS" shown at the top and then in the

following 2 boxes so that a checkmark appears in both of them: "DISPLAY EQUATION ON CHART" and "DISPLAY R-SQUARED VALUE ON CHART." Hit "OK."

5. To reposition the equation of the line and the R-squared value on the graph, just click on either "y =" or "R-squared="

and drag the corner squares with the mouse.

4. Repeat the above steps with the new data set taken from Chart #2, as shown

Day #10: Closure: Hold a class discussion by asking the students which method they

1. Summary Essay: Students are to write an essay describing what they

learned and did during the two weeks of this instructional unit. They should include an analysis of their data as well as a comparison of the two different methods of finding a "line of best fit" to a data set – the traditional "paper & pencil" method as opposed to a computerized one.

2. Computer Project: Students are to choose two data sets – one linear,

Additional Comments and Suggestions – See "Section 3.1: Linear Function Graphing

Lab" and "Section #3.1(a): Quadratic Function Graphing Lab," which offer other

computerized graphing lesson ideas that can be used in the classroom using MS

EXCEL. These labs are located in Appendix 7.