Monday, December 9, 2013

Excel : Line of Best Fit

You can easily use Microsoft Excel to find the equation for the line of best fit for experimental data points.

Remember that the equation of a straight line is y = mx + b
where m is the slope (gradient) of the line and b is the intercept on the y axis (that is, when x =0).

Microsoft excel will calculate both the slope of the line, m, and the y-intercept, b, for you.

First you will need to open up a new excel spreadsheet and set up your data.
Here we have used Row 1 of columns A and B for our headings (x values and y values).
Various values for x and y have then been entered into the cells: 

cellAB
1x valuesy values
21.0211.0
32.0521.3
42.9631.2
54.1239.7
64.8653.4

Next, we highlight any two adjacent cells (empty cells) somewhere else in the same spreadsheet, for example A8 and B8

cellAB
1x valuesy values
21.0211.0
32.0521.3
42.9631.2
54.1239.7
64.8653.4
7
8 

Next to the fx symbol above the cell headings A, B, etc, type in =LINEST(B2:B6, A2:A6)
 then press Ctrl+Shift+Enter simultaneously.
(B2 is the first  y value to be used in the calculation and B6 is the last y value to be used in the calculation. Similarly, A2 is the first of the x values to be used in the calculation and A6 is the last of the x values to be used in the calculation.)
Your spreadsheet should now look this:

cellAB
1x valuesy values
21.0211.0
32.0521.3
42.9631.2
54.1239.7
64.8653.4
7
810.50606 -0.21918

The number in cell A8 is the slope (m) of the line of best fit.
The number in cell B8 is the y-intercept (b) of the line of best fit, that is, the point with coordinates (0,b)
The equation for the line of best fit for this data is y = 10.50606x -0.21918

In order to draw the line of best fit for the experimental data above, we only need to calculate new values for y using the equation for the line of best fit (that is, the first and last points on the line of best fit).:
given x=1.02 ,then,  y = (10.50606 x 1.02) -0.21918 = 10.5
and x=4.86 , then, y =(10.50606 x 4.86) -0.21918 = 50.8

cellABC
1x valuesy valuesnew y values
21.0211.010.5
32.0521.3
42.9631.2
54.1239.7
64.8653.450.8
7
810.50606 -0.21918

Plot the points (1.02, 10.5) and (4.86, 50.8) on the graph of experimental data and draw a straight line between these two points. This is the line of best fit for the experimental data in the table.

Further Activities:
Go to http://www.ausetute.com.au/members/bestfitline.html

1/ Enter the data above into the tool for calculating the two points needed to draw a line of best fit. Check the new y values above, and see what the graph looks like.

Graph the data points in questions 2 to 5 below and draw the line of best fit:

2/ (0.23, 9.75), (0.94, 6.39), (1.77, 5.73), (4.59, 4.91), (11.52, 0.33)
3/
time / minmass / g
0102.8
273.4
448.7
627.3
810.9

4/
volume / mL5.010.015.020.025.0
mass / g3.27.19.413.514.8

5/ At time 0, the temperature of a reaction mixture was 25oC. The temperature of the reaction mixture was then recorded every minute for 4 minutes. The results of the experiment were temperatures of 29oC, 32oC, 35oC and 40 oC.

No comments:

Post a Comment