You will be provided with a graph of a set of measured data, y vs x, for which theory predicts that y = mx+b. Of course, each measurement has an uncertainty of the kind we discussed last term, and so we would be surprised if all of the data fell onto a perfectly straight line! The question then becomes what line “best” defines the data (and for that matter, how is “best” defined?)

Download and open “LSF plot” in a software such as GIMP, Paint, PowerPoint, OneNote etc… You want a software where you can draw a line on the graph.

a) Draw a sensible line through the data.

b) Use the grid and scale to estimate the slope and intercept and write an equation which relates y and x.

The standard way to design a best fit line is to minimize the sum of the squares of the deviations. Let’s see how well you did with your fit.

The data from Exercise 1 is in this Excel file that you can download.

a) Label column C of the spreadsheet: y calculated and use the equation of the line that you proposed in Exercise 1 to calculate the predicted value of y at each x. (It will save you some time in Exercise 3 if you put the slope and intercept into separate cells, and then build the linear equation with them as constants. Remember that to copy a constant from cell to cell, put a dollar sign in front of both the row and column, e.g. $G$1.)

b) Make a scatter plot in Excel which shows the actual data (as data points) and your line (as a solid line without markers).

c) Label column D deviation and calculate the difference (i.e. the deviation) of each measured y value from its predicted y value.

d) Label column E deviation^2, and calculate the square of each deviation.

e) At the bottom of column E, sum the squares.

Now, Excel has a built-in function which will perform a least-squares analysis for you.

a) On your graph, right-click on one of the data points. Excel should provide you a menu of options, one of which is Add Trendline . Choose a linear fit and the options to display the equation on the chart, and to show R².

b) Change the slope and intercept for the calculated y column to the values of your trendline and check the sum of the squares of the deviations for the Excel fit line. How does your line compare?

There are a number of assumptions built into the fit. The calculation assumes that the uncertainties on x are negligible compared to the uncertainties on y, that the uncertainties on y have a Gaussian distribution, and that all of the y values have the same uncertainty.

a) You may have noticed (and been appalled!) that there were no uncertainties specified on the measured values. If the assumptions in exercise 3 are justified, then the measured deviations should be an indication of the uncertainty on each measurement. What might be a reasonable estimate of that uncertainty on each measurement?

b) In Excel, you can add error bars to a graph. If you click anywhere inside the graph, you should activate a Chart Tools menu. (Mine shows up at the very top of the screen. You might have to look around in your version.) Under Layout choose Error Bars. In this case, you should use either a Fixed Value or a Custom Value (it might be useful to understand how both options work) for all of the data points. (Use your estimate from part (a)).

There is one important question left: what are the uncertainties on the best fit slope and intercept? (If the individual measurements have uncertainties, then surely any line fit to them will have uncertainties as well.)

In principle, you could estimate these by hand. You could look at your original graph, and ask: “What line could I put through the data that has a bigger slope but is still a reasonably good description of the data? What about a line with a smaller slope…?” Clearly there is room for variation since other students in the class (perhaps even at your table) came up with “best fit” lines that were different than yours. You could use these limits to estimate the uncertainty on the slope and intercept.