Document Sample

LSP 120: Quantitative Reasoning and Technological Literacy Topic 1: Introduction to Quantitative Reasoning and Linear Models Lecture Notes 1.3 Prepared by Ozlem Elgun 1 Adding a Trendline Using Excel • Open the file: MileRecordsUpdate.xls and calculate the slope (rate of change) in column C. • Is this women’s data perfectly linear? • No, there is not a constant rate of change. (See table below.) Prepared by Ozlem Elgun 2 Calculating rate of change Women's Date Record seconds Change 1967 277 1969 276 -0.50 1971 275 -0.50 1973 269 -3.00 1979 262 -1.17 1981 261 -0.50 1985 257 -1.00 1989 255 -0.50 1996 253 -0.29 Graphing the data produces the following graph which confirms that the data is not perfectly linear. To graph data, highlight the data you want to graph (not headers or empty cells). Choose a chart type: Under the Insert tab click on Scatter located under the Charts group. Under Scatter, choose Scatter with only Markers (the first option). A simple graph is created. Prepared by Ozlem Elgun 3 280 275 270 265 Series1 260 255 250 1965 1970 1975 1980 1985 1990 1995 2000 We can clearly see that the data is not linear but we can use a linear model to approximate the data. You will need to add a title, axis labels and trendline (including the equation and r-squared value). First click on the graph to activate the Chart Tools menu and then choose the Design tab. Under the Charts Layout group, select #9. (Click on the "more" arrow to display all eleven layouts. Slide over each graph should look layout until you locate #9.) Your Prepared by Ozlem Elgun like this: 4 Chart Title 280 275 270 265 Axis Title Series1 260 Linear (Series1) 255 250 y = -0.929x + 2103.4 R² = 0.9342 245 1965 1970 1975 1980 1985 1990 1995 2000 Axis Title Prepared by Ozlem Elgun 5 • Click on the Chart Title and add a descriptive title (consider who, what, where and when). Click on each Axis Title and label both your x-axis (horizontal axis) and your y-axis (vertical axis). If you are graphing only one series of data, always be certain to remove the legend (just click on the legend and use either the delete or backspace button). To move the equation/r-squared value slide on the text box containing both the equation/r-squared value. Once your cursor changes to "cross-hairs" press on the left mouse button and slide the text box to a location on the graph where it is easier to read. • It is suggested that you remove the minor axis gridline by changing them to the same color as your background. Right-click on the y-axis (vertical axis), choose Format Minor Gridlines then Solid Line. Change the color of the line to match your background (currently your background is white). • It is important to add a text box stating the data source used to create the graph. Under the Insert tab choose text box under the Text group. Draw a text box on your chart and then type in "Source:" followed by the data source. If no data source is listed, type "Unknown". Prepared by Ozlem Elgun 6 Women's World Records in the Mile Run from 1967 through 1996 280 275 y = -0.929x + 2103.4 R² = 0.9342 270 Record in seconds 265 260 255 250 245 1965 1970 1975 1980 1985 1990 1995 2000 Source: USA Track and Field Year Prepared by Ozlem Elgun 7 In the preceding graph… • The black trendline is the line that “best fits” the data. It is a line that comes as close the all the data points as possible. • The R2 value indicates how linear the data actually is. The R2 value will be a decimal between 0 and 1. The closer it is to one, the closer the data is to linear. The smaller the R2 value, the less linear the data. We can see here that the R2 value for the women’s mile record is .9342 which is very close to one, so the data is very close to linear. • The equation is the equation of the trendline in y = mx + b form. We can see that the slope or the rate of change of the trendline is - .929 which means that according to the trendline, the mile record is decreasing by just under 1 second every year. Prepared by Ozlem Elgun 8 Use excel functions, not the equation given in the graph to calculate future predictions You learned in class to use the =slope() and +intercept() functions. You should use the slope and intercept functions when you are modeling and calculating predictions because the equation that Excel puts on the graph is often rounded to only a few decimal places. Using the equation that Excel puts on the graph can lead to aberrant results because of this rounding. Prepared by Ozlem Elgun 9 Why do we add a trendline and how do we use it? • Since the trendline is an approximation what is happening with data, we can use it to make predictions about the data. • For example, to predict what the mile record was in 1999, use the equation of the trendline. First identify the variables. X is year and Y is record in seconds. Calculate slope and intercept on Excel. Then plug 1999 in for X in the linear equation and solve for Y. Prepared by Ozlem Elgun 10 Five guidelines to see if the trendline a good fit for the data • Guideline 1: Do you have at least 7 data points? • Guideline 2: Does the R2 value indicate a relationship? Reminder: R2 is the percentage of variance of y that is explained by our trendline. It is a standard measure of how well the trendline fits the data. • Guideline 3: Verify that your trendline fits the shape of your graph. • Guideline 4: Look for outliers • Guideline 5: Use practical knowledge/ common sense to evaluate your findings Prepared by Ozlem Elgun 11 Justifying your prediction in words Once we calculate the answer to the question, we cannot simply report the numbers. We need to present them in meaningful sentences that explain their meaning in their contexts. SAMPLE LEAD SENTENCES “If the trend established from 1967- 1996 persists, we expect the Women’s world record to be ----------- seconds in 1998. “ SUPPORTING SENTENCES “We are confident in our prediction because the r-squared value of ---------- shows that the data has a strong/ moderate/weak linear relationship. Even though in the long term we expect the rate of change in women’s mile records to decrease and not stay constant, we expect that in the very near future the linear trend should continue, giving us confidence in our prediction. ITEMS THAT MUST BE POINTED OUT WHEN APPLICABLE Reason for using less than 7 data points. Omitting any single data point. Focusing on a localized linear trend. Continuing to predict a higher amount when they trend actually decreases (or the opposite). Prepared by Ozlem Elgun 12 Self Practice: Adding a Trendline (in Excel 2007) • Open the file: MileRecordsUpdate.xls and calculate the slope (average rate of change) in column H for Men’s World records in the Mile Run. • Is this men’s data perfectly linear? • Can you use a linear model to describe the data? (Hint: Graph the data in a simple scatter plot) • Create a graph with a trendline, title your graph appropriately. • What would the men’s world record be in the year 2000? (Hint: in your calculations you need to use the SLOPE and INTERCEPT Excel functions, and use the linear equation.) • Check you answer by extending the trendline to year 2000. (right click on trendline, under forecast, increase it forward by number of units you need to, to reach 2000). Does your trendline show a similar number as your prediction. • Once you calculate your answers write your answers our in meaningful sentences, justifying your prediction in words. (Hint: report your prediction, the R-squared value, and any possible caveats.) Prepared by Ozlem Elgun 13

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 7 |

posted: | 12/11/2012 |

language: | English |

pages: | 13 |

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.