Since it appears that the data is linear, let’s check that out by inserting a trendline on the graph. The trendline is a statistical procedure that produces the best fit linear line to a set of data. You will notice that once the trendline is inserted, it does not connect the dots but draws the line that best fits the data. We will also insert the equation and the r-squared on the graph. Remember that the equation of a line is always in the form of y = mx+b. The trendline (or linear regression will calculate the m (slope) and b (y-intercept).
To add the trendline:
A. Right click on one (any one will do) of the data points on the graph. This will bring up a menu (of course) and select Add Trendline as shown in the figure.
B. You will now get a new dialog box. Highlight the Linear box ...
... and then press the Options tab at the top. Check the Display Equation on Chart and the Display R-squared value on chart as shown below.
C. Press OK and a line with an equation and r-squared value will appear on the chart. It should look like this:
Note that the line is linear and does not just "connect the dots" but is the "best fit" line for the data. The equation for the line is y = 0.1045x + 0.5238. Note: your values may be different. The R-squared value is a statistical measurement that measures how well the data fits this line. This value ranges between 1 and -1. The closer to either 1 (or -1), the better the data fits the data. In this case, the R-squared value is 0.9815, which is close to 1 and therefore we can conclude that this data is linear. We generally want the R-squared value to be larger than 0.9.
5. Now that you have the equation for the data, you can use this data to calculate where I should have been at 15 minutes, 50 minutes and 146 minutes. This is the power of trendline analysis, one can make a prediction from the data. Since we know the equation is y = 0.1045x + 0.5238 and we know that the X values are times and Y values are distances, we can simply insert one of the times (say the 15 min) and substitute for the X in the equation. This would be: y = (0.1045)(15) + 0.5238. Evaluate the equation and you will have the distance that I would have gone after 15 minutes ( I got 2.09 miles). To do this, simply go back to Quiz Sheet sheet and make an equation in cell and "drag the box) to copy the calculation. This was done below (note, you can see the equation in the formula box).
Please do this and email me the file. Note, your numbers will NOT be the same as shown here.
Back a page
Back to home