Not all relationships are linear, that is represented by a straight trendline on an X-Y chart. The astronomer Kepler investigated the relationship between the distance of planets from the sun and the length of their orbits. If we enter these data into a spreadsheet, plot them on an X-Y chart and add a linear trendline we can investigate the nature of the relationship.
The first attempt shows a linear trendline through the data and we can see that this is not a particularly good fit as the data seem to curve under the line and then through it.
We change the trendline by clicking on it with the right mouse button (or double clicking with the left button) and choosing the Type tab. The other five types of trendline involve more advanced maths but we can skip that here by choosing the Power option. The trendline now follows the data almost exactly.
Kepler discovered that the length of planetary orbit squared was proportional to the cube of the distance from the sun. We can do the calculations quickly in Excel and plot the new data. These data show a linear trend so we add a linear trendline. The Index in column F confirms that the relationship because the ratio between Distance3 and Time2 is constant - or very nearly. For columns B and C it varies a lot; to test this insert a column between C and D and create another index. Click the ‘D’ panel and choose Insert/Columns.
Open the file and Complete the formulae for D^3 and T^2. Compare lines drawn from D&T with D^3 & T^2.
Open the moons.xls file and investigate whether the same relationship between distance and length of orbit holds for other planetary bodies.
Use the Back Button to Return to the previous page