Excel Graphing

Prepare a chart of your data like the one below

A. Select data to graph: Highlight the data you wish to graph, with the x axis values should be the first column (which they are). Note that the titles are highlighted as well.

B. Use the Menu item INSERT and choose CHART

C. You will now go through a series of dialog boxes. the first one selects Chart Type: we always choose Scatter and for this one, choose the picture that has data points with no lines, as shown below.

D. Press the Next button to get the next box. This just shows the Source Data - since you highlighted the data to graph initially, you do not need to do anything on this box. Press next.

E. The third box allows you to add labels and format the graph (a bit). Put in labels for the Title of the graph (whatever you feel is appropriate), and labels for each of the axes. This is shown below.

F. Most scientific graphs usually do not have gridlines, so lets remove them. Press the Gridlines tab at the top of this dialog box. Uncheck the MAJOR GRIDLINES box - this will remove them. We will not make any other changes at this time, but you may wish to browse through the different tabs and note what you can do with them. Press the Next button.

G. I always like to have the chart on a new sheet, so in the last box, choose that option and press Finish. Your graph will now appear on a new sheet.

Linear Regression or Trendlines 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).