Linearising Data

Introduction

In general it is best for graphs to show a linear relationship ie. relationships where the y values are directly proportional the x values. This makes it much easier to see deviations from a straight line. It also makes it possible to express the relationship between the experimental quantities and those predicted by theory. For data which is non-linear it is first necessary to linearise it. This is illustrated in figure 1 and 2.

Fig. 1: The distance a ball bearing fell as a function of time in Earth's gravitational field.

The data is clearly non-linear, but it is difficult to determine what the relation is.

Fig. 2: The distance a ball bearing fell as a function of time² in Earth's gravitional field.

By plotting time² against distance, shown in figure 2, we can clearly see a linear trend. Therefore we can determine that the trend is an x² power law.

Using Excel

"Consider the data shown in this spreadsheet. It shows data from an object accelerating as a result of Earth's gravitational field (errors on each individual point have been ignored)."

"Use this data to compute g, acceleration due to gravity, and its associated error."

How do we go about doing this? Well, firstly we have to consider a suitable model.*

The object is falling a small distance relative to the radius of the Earth. Therefore, it is a reasonable approximation to assume that g is a constant. From the equations of motion for constant acceleration (or "SUVAT" equations) we know that

, (1)

Where all the symbols have their usual meaning (s is distance, u is the initial velocity etc).

Given that u = 0 in this case, we can reduce this to

, (2)

This is now in a suitable form to linearise. Compare this to the equation for a straight line

, (3)

If we set y to be our dependent variable s and x to be t², the result is a gradient whose value corresponds to half the acceleration, g, and the intercept c = 0. I.e.

, (4)

So how do we do this in Excel?

1.Create a new column on the spreadsheet for the time² variable. Then in the top row, type in the command to square the time data point on that row, i.e. in this case "=B7^2". This is shown in figure 3. Press enter to accept the function.

Fig. 3: The equation that will square the first element in the time column.

2.Double click the small square circled in red on figure 4 so that it repeats the calculation across all the time data points

Fig. 4: The square to press to extend the calculation across multiple data points.

Fig. 5: The data that we obtain when all the time data points are squared.

3. Apply the linest function to the data, where the y variable is s and the x variable is t² (not just t).

If you are unsure how to do this, follow the guide for using linest available [link required]

You should end up with the data shown in figure 6.

Fig. 6: The result of the linest calculation on our data

4. Using equation 4, we know that the gradient, m, is half of the acceleration due to gravity g. Therefore multiply m, and its error, by 2 to obtain our measured value for g.This should give you a final answer of

(9.8 ± 0.3) m s^-2