CS 103 Day 13, Monday April 22, 2001

• Fitting a line to a scatterplot is a very common way of expressing a generalization and summarization of the relationship between the two variables.
• In the sciences, the standard "best fit" line is called the "regression line,"
• in business and management, the term "trendline" is used. Same line.
• Curved lines can be used but we will stick to straight lines here.
• Which axes you use is important. 
  The line-fitting assumes that you want to estimate or predict a y-value from an x-value.  Not the other way!
• The residual for a point is the (signed) distance from the point to the line (vertically!)
• The regression line is the line that minimizes the sum of the squared residuals.
(Move some points in the Residuals sheet.  Switch to the Squares sheet, examine, return.)
• Why use this line? Main reason: everyone who fits it will get the same line (vs. eyeballed line)
• Problem:  values toward the ends of the line may have disproportional effect on slope.  
          (Line up all the points on a line;  then move the end point.)
• Extrapolation (forecasting) is dangerous--straight line mechanism may not hold outside the range of the data.
• Slope of the line = coefficient of x (the number that multiplies x in the formula).
• Slope = change in y per unit change in x  (remember Aluminum data) = rise/run.
   (Open Regression Slope.xls.  Examine.  Move points and check it always works.)
  Excel example (noninteractive as web page.)
• R²  "coefficient of determination"  measures proportion of variability in y-values which is explained/predicted by knowing the x values and using the regression line.  (p. 243)
• How do we measure "proportion of variability?"  Open Residuals RSquared.xls. Rsquared tab. Study it.

• Read pp. 236-239, 243, for background.
• DO PP. 240-241.

Follow the instructions on p. 240 to get the data table of Figure 8-3.
• On p. 241, modify the instructions as needed for the new Chart Wizard, to get the scatterplot in Figure 8-4. Change the scale on the x-axis to have a minimum of 20, and on the y-axis to have a minimum of 40.
• Add trendline, by right-clicking on the data series (any data point) to get a menu showing this choice, or by choosing it from the Chart menu.
• p. 242 is correct as written:
• Choose the Linear box (showing the straight line).
• From Options, choose those options shown on p. 242..
• Note: The line will start at the x-value for the smallest (horizontally) data point, end at the x-value for the largest data point. To make it extend farther, i.e."Forecast" ("extrapolate" to scientists), change the boxes in the Forecast area.

• Correlation, more regression (Optional)

If you have used or expect to use correlation coefficients, you may want to try pp. 243-247.
The analysis ToolPak can also grind out some fancier statistics, pp.248ff.

Assignment 9 and the Learning Excel notes are available as a handout.

Assignment 9 (regression line, Rsquared) Due Friday, Day 15, April 27 #5, p. 262 (Data on used Mustangs) Do these problems rather than those in the book.  A. a. Plot Price against Age (age on the x-axis, since you would expect age to predict or determine price, not the other way around)  Look at the pattern. Then: b. Put a linear regression line (trendline) on the plot, and show the equation of the line and R2 as well.  c. Print your graph; on the paper answer these questions:   i. How well does the line visually describe/summarize the dependency of price on age?   ii. Interpret the size of R2 in light of your answer to (i) (See p.243, coefficient. of determination.)   iii. Ignore the line. Describe in words how price depends on age. You might say, "When a car is newish, the price is… As it gets older, the price ……. For the oldest cars, ……." B Repeat questions a, b, c above (printing graph with regression line, discussing relationship and R2).. using just the cars of age less than 10 years.   To get the new graph, you can just select these newer cars, since the data is already sorted on age. Or (Save your workbook as E8Mustang, then save it again as E8DMustang before deleting, so you don't lose the original work) you can delete all the older cars if you prefer.  d. Also answer this question. "(For Mustangs less than 10 years old), the price drops $______ for every year older the car is, on average." (Hint: slope of line…)