Dealing with uncertainty
It is, I know, rather hard work wading through pages full of screen images. I apologise for that, and assume that you will only be reading this if you actually want to know how to deal with uncertainty. It isn't for the faint-hearted or the casual browser.
During an experiment you will take various readings. These constitute the raw data. Often you will find yourself recording them in tabular form. Suppose, for example, that you have data for a simple pendulum.
To calculate the period, we must find the mean of the three measured times and then divide by 20, so we enter
into cell E2 and then copy down. This is a calculated value and cannot have any more than 4 significant figures because that is the number of significant figures in columns B, C and D from which column E is calculated. So we have to set column E to 3 decimal places.
(You will find that these spreadsheets have limited functionality. For example, you can double click on a cell to see the formula it contains. Feel free to experiment: I don't think that changes you make are recorded permanently.)
Now we have to enter the absolute errors. For the length, the experimenter might decide that holding the metre rule against the string in mid air is tricky, and that he only has confidence in measurements to the nearest 3 mm in spite of the fact that the rule is calibrated in mm. So we insert a column between A and B and fill it with 0.3 all the way down.
For the stop-watch readings he might well reckon that the instrument is intrinsically accurate to the nearest centisecond, so he has recorded exactly what the stop-watch said. On the other hand, it is clear from the data that there has been some reaction time error, so we need to reach a view on the uncertainty values here. One way would be to subtract the smallest value from the largest, halve it and then divide by 20. Another is to take the standard deviation by entering the formula
=stdev(B2:D2)/20 (or stdev(C2:E2) if the length error column has already been inserted!)
and that is what has been done here
Theory suggests that the relevant equation for a pendulum is
T 2 = 4 p2 l / g
So a sensible strategy would be to plot T 2 against l : then we can calculate g by knowing that the gradient is 4p2/g. We accordingly establish a new column which we fill with T 2 values. There is now the problem of how to calculate the uncertainties in T 2.
The key idea now is that of fractional uncertainty, and there are two fundamental equations
absolute uncertainty = fractional uncertainty ´ absolute value
overall fractional uncertainty = sum of component fractional uncertainties
In order to use these formulae easily, it is a good idea to establish fractional uncertainty columns for all relevant values, both raw and calculated. Here is the table with a T 2 column and fractional uncertainty columns for l and T.
The second of the two uncertainty equations (together with Note 2) tells us that the fractional uncertainty in T 2 is twice the fractional uncertainty in T , so that has been worked out in column K on the next table, although, once you get used to the system, you wouldn't normally bother to fill it in explicitly. The formula in K2 is
= 2 * i2
Finally, we use the first uncertainty formula to calculate the absolute uncertainties of T 2, which is what we need for the graph-plotting. To do this we enter in column L the formula
= J2 * K2
All of this has been hard work. But now that the table has been set up, it is relatively easy to add new things. Suppose we decide to work out values of g directly in the table from the formula
g = 4 p2 l / T 2
Column M calculates these values in the ordinary way. The second rule tells as that
fractional uncertainty in g = fractional uncertainty in l + (2 x fractional uncertainty in T )
We can work this out from the formula
= c2 + 2 * i2
Then if we multiply this by the actual value from column M, we get the absolute uncertainty of g (using the first uncertainty equation). So column N contains the equation
= m2*(c2 + 2 * i2)
Notice three features of these results:
The values of g are in cm / s2 because the lengths were measured in cm.
The values of the gradient are, on the whole, increasing. This is because the lengths are the lengths of the string and should really have the bob radius added to them. A value for this will emerge shortly.
Since the uncertainty begins in the tens, there is absolutely no point in quoting g values beyond the units. In fact there isn't really any justification for quoting the units (in the hundreds / tens / units sense) at all, but Excel doesn't handle significant figures easily, so we'll live with three-figure accuracy.
The main point is that, once we've got fractional uncertainty columns associated with the raw reading columns, calculating uncertainties of composite functions is pretty straightforward.
Coming soon : uncertainty bars on graphs, uncertainties from standard deviations