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: 

cell	A	B
1	x values	y values
2	1.02	11.0
3	2.05	21.3
4	2.96	31.2
5	4.12	39.7
6	4.86	53.4

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

cell	A	B
1	x values	y values
2	1.02	11.0
3	2.05	21.3
4	2.96	31.2
5	4.12	39.7
6	4.86	53.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:

cell	A	B
1	x values	y values
2	1.02	11.0
3	2.05	21.3
4	2.96	31.2
5	4.12	39.7
6	4.86	53.4
7
8	10.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

cell	A	B	C
1	x values	y values	new y values
2	1.02	11.0	10.5
3	2.05	21.3
4	2.96	31.2
5	4.12	39.7
6	4.86	53.4	50.8
7
8	10.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 / min	mass / g
0	102.8
2	73.4
4	48.7
6	27.3
8	10.9

4/

volume / mL	5.0	10.0	15.0	20.0	25.0
mass / g	3.2	7.1	9.4	13.5	14.8

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