{"id":2145,"date":"2023-04-26T14:40:48","date_gmt":"2023-04-26T14:40:48","guid":{"rendered":"https:\/\/content.one.lumenlearning.com\/quantitativereasoning\/?post_type=chapter&p=2145"},"modified":"2025-08-28T20:43:59","modified_gmt":"2025-08-28T20:43:59","slug":"modeling-linear-growth-learn-it-4","status":"web-only","type":"chapter","link":"https:\/\/content.one.lumenlearning.com\/quantitativereasoning\/chapter\/modeling-linear-growth-learn-it-4\/","title":{"raw":"Modeling Linear Growth: Learn It 4","rendered":"Modeling Linear Growth: Learn It 4"},"content":{"raw":"

Spreadsheet Hands-On: Use Excel to find the regression line<\/h2>\r\n

Let's look at an example of using a spreadsheet to perform linear regression on a data set.<\/p>\r\n

The following set of data represents the annual sales of a company in millions of dollars since the year 2003. Let [latex]y[\/latex] represent the quantity of sales and let [latex]x[\/latex] represent the number of years since 2003.<\/p>\r\n\r\n\r\n\r\n\r\n
[latex]x[\/latex]<\/td>\r\n[latex]0[\/latex]<\/td>\r\n[latex]1[\/latex]<\/td>\r\n[latex]2[\/latex]<\/td>\r\n[latex]3[\/latex]<\/td>\r\n[latex]4[\/latex]<\/td>\r\n[latex]5[\/latex]<\/td>\r\n<\/tr>\r\n
[latex]y[\/latex]<\/td>\r\n[latex]733[\/latex]<\/td>\r\n[latex]841[\/latex]<\/td>\r\n[latex]964[\/latex]<\/td>\r\n[latex]1162[\/latex]<\/td>\r\n[latex]1346[\/latex]<\/td>\r\n[latex]1529[\/latex]<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n

 <\/p>\r\n

Using the techniques you learned in the previous section, store the data in a spreadsheet and use the computer to find the regression line (also called a trendline).<\/p>\r\n

Step 1: Store the data<\/h3>\r\n
    \r\n\t
  1. As you did before, label two columns with the the input and the output of the data table. Then highlight both columns at once and choose Scatter with Smooth Lines.
    \r\n

     <\/p>\r\n

    \r\n[caption id=\"attachment_2149\" align=\"aligncenter\" width=\"727\"]\"screenshot Figure 1. Create a scatter plot[\/caption]\r\n<\/center>\r\n

     <\/p>\r\n<\/li>\r\n\t

  2. Click on the chart, then on the style icon with the plus-sign on it, hover over Trendline, click the right arrow, and choose More Options... to open the Format Trendline pane.\r\n\r\n

     <\/p>\r\n

    \r\n[caption id=\"attachment_2150\" align=\"aligncenter\" width=\"751\"]\"screenshot Figure 2. Create a trendline[\/caption]\r\n<\/center>\r\n

     <\/p>\r\n<\/li>\r\n\t

  3. Choose Linear under Trendline Options, then click Display Equation on chart and Display R-squared value on chart.
    \r\n

     <\/p>\r\n

    \r\n[caption id=\"attachment_2151\" align=\"aligncenter\" width=\"775\"]\"screenshot Figure 3. Add the function equation[\/caption]\r\n<\/center>\r\n

     <\/p>\r\n<\/li>\r\n\t

  4. Note that the equation of the regression line is [latex]y = 162.66x+689.19[\/latex] and the [latex]r^2[\/latex] value is [latex]0.9881[\/latex] (Excel uses a capital R for the coefficient of determination, but we will use a lower-case [latex]r[\/latex]). What does the coefficient of determination [latex]r^2[\/latex] tell us about the appropriateness of the regression line to model the data? Since [latex]0.9881[\/latex] is very close to [latex]1[\/latex], this model is a good fit for the data. A coefficient of determination greater than [latex]0.7[\/latex] is usually considered good, but there are cases in which a large\u00a0\u00a0[latex]r^2[\/latex] doesn't explain well and when a small\u00a0[latex]r^2[\/latex] is considered sufficient. Each situation is different.<\/li>\r\n<\/ol>\r\n

    In this case, we can use the model to make predictions about the situation. It should be used with caution though, as the only factual information we possess is the data collected. When we make predictions outside a data set, we are said to be\u00a0extrapolating<\/strong>\u00a0from the data, which is irresponsible to rely upon as though the prediction is factual. Making predictions within the known data, called\u00a0<\/span>interpolating<\/strong>, is much safer since models tend to experience\u00a0<\/span>model breakdown<\/strong>, an input beyond which the predicted output does not make sense.<\/span><\/p>\r\n

    We can also use graphing calculators to perform linear regression. The following video provides an example.<\/p>\r\n