Data Matters with Excel®
Activity 9.1
Excel can find lines that describe, or fit to, points that line up on a scatter plot. Excel can also find lines that fit to points that do not line up, but for this project your task is to get comfortable with using the software to find lines that fit to points that can be on lines. In fact, you will be looking only at pairs of points and finding the lines that go through both points of each pair.
For the exercise, find the equations of lines and plot those lines for each school district in the following table. Your x-axis variable is Lunch Rate, and your y-axis variable is Reading Pass Rate.
|
Table 9.1.4 (from Data Matters)
Schools with Highest and Lowest Subsidized-Lunch Rates
|
|
|
District
|
School |
Lunch Rate |
Reading Test
Pass Rate
|
|
|
Auburn
|
Lake View |
19% |
59%
|
| Terminal Park |
59% |
42%
|
|
| Bainbridge |
Wilkes |
4% |
63% |
| Ordway |
8% |
77% |
|
| Bellevue |
Somerset |
2% |
82% |
| Stevenson |
49% |
49%
|
|
| Edmonds |
Brier |
9% |
65% |
|
Cedar Valley |
83% |
18% |
|
Label column A Lunch Rate and column B Pass Rate. Enter the data for a district in rows 2 and 3.
Click on the chart wizard. Select XY (Scatter) and click on Next. Click on Series and remove any previous series. Click on Add. Click on the tiny red arrow to the right of the X Values box. Select Lunch Rates and press “Enter.” Repeat those steps to enter the y-values (the pass rates). Click on Next, Finish.
Right-click on a dot and select Add Trendline. Click on Options and select Display Equation on Chart. Click OK. The regression line and equation appear on the chart.
To get the equations for the rest of the districts, edit the data in columns A and B.
| ©2008 Key College Publishing. All rights reserved. |
|
