How to plot a Best-Fit Line using Excel 2007
Consider the following example:
You want to find out how fast a turtle can move in a straight path. By using a stop watch and a tape measure you are able to get the following data:
Time (s) / Total Distance Traveled(m)
0 / 0
120 / 8
240 / 19
360 / 27.9
480 / 42.1
660 / 60
The goal is to find the average speed of the turtle from the equation distance = speed ∙ time, or
d = vt
Notice that this equation is in the form of the equation for a line with the y-intercept = 0:
y = mx + b
where m is the slope and b is the y-intercept.
To find the speed v, one needs to plot the distance d (y-axis) as a function of time t (x-axis) and find the straight line fit. The slope of that line is equal to the speed v.
Follow the steps shown below to make a graph with a straight line that fits your data. Excel will calculate the slope of this line.
A. Start MS Excel 2007.
B. Enter your data into Excel spreadsheet. By default, column “A” is x-axis data and column “B” is y-axis data. In our example, the first column (A) contains values of time t, whereas the second column (B) contains values of distance d.
C. Highlight all cells containing data.
D. Click on the Insert Tab then select the Scatter button from the Charts group. Use the first type of scatter charts - "Scatter with only Markers".
You should see a simple plot prepared by Excel 2007 similar to this:
E. To add a title and x and y axis labels to the graph select Layout tab from Chart Tools. Add a header using the "Chart Title" button. Add x and y axis labels (don’t forget to include units) using the "Axis Titles" button. Optionally, you may edit or simply remove the legend “Series 1” by clicking on it the Legend tab. You can also grab and drag a corner of the graph (chart) to change its size.
F. To add the linear fit (a straight line fit) to your graph (chart) click once anywhere inside the graph area. Select the Layout tab from Chart Tools. Click on the Analysis icon then choose Trendline then Linear Trendline. (Or if you have a "Trendline" icon click on it and select the "Linear Trendline" option).
You should see a graph similar to this:
G. To show the equation of the best-fit line on your graph (chart). Go back to "Trendline" and select "More Trendline Options..." Then check the "Display Equation on chart" box.
The final result should look similar to the example shown below.
From the equation for that straight line y = 0.0918x - 2.2788 we can conclude that the best estimate of the average speed is: v = 0.0918 (m/s), where 0.0918 (m/s) is the slope of the line and -2.2788 (m) represents the y-intercept.
1