Graphical Analysis

Because of the inherent uncertainty of an experimental measurement, the data point plotted in a graph could be anywhere in the box formed by the horizontal and vertical error bars. This also generates an uncertainty in the slope and y-intercept of the best fit line.

Using Excel:
Given n data points (xi, yi), we want to find the equation for the “best” curve for this set of data. If the data is linearly related, then the process is called linear regression. In general, data points are not linearly related and the process of obtaining the equation for the best curve is called nonlinear regression.

To make Excel calculate the slope, intercept and uncertainties of the best fitting line, you must use the array function called LINEST.

Select 4 cells in the Excel spreadsheet where you want the calculation to be performed. Then enter the following command

=linest(Range(y), Range(x), True, True)

Range(y) represents the cells containing the dependent variable, and Range(x) the cells containing the independent variable. The two next elements are logical operators. If the first one is True, it means Excel does not force the slope through (0,0). If it was False, it would force it through the origin. The second True, tells Excel to calculate the uncertainties on the slope and intercept.

To evaluate an array function you must use CONTROL+SHIFT+ENTER. It will then fill the four cells with the following information

Slope intercept
uncertainty on slope uncertainty on intercept

Using LINEST with iWork Numbers on the Mac :
Here is a file you can download to help you use theLINEST function on Numbers.

Alternate method:
When the uncertainties are large and you do not have access to a computer, there is a simple method to evaluate the uncertainties on the slope.

This method greatly overestimates the uncertainties on the best fit line.

Note that three possible straight lines can be drawn in the previous graph:

  1. the line of best fit (best slope = m),
  2. the line of maximum slope (slope = m1) and,
  3. the line of minimum slope (slope = m2).

The slope, therefore, has an uncertainty calculated as (m1-m2)/2.
The proper way to quote the value of the slope would then be

m ± (m1-m2)/2.

Note that the lines of maximum and minimum slopes are drawn so as not to fall outside the uncertainty bars. This may not always be possible, in which case, some common sense must be applied in drawing these lines.