In the next example we examine the relationship between altitude and atmospheric pressure. As before we enter the data, make an X-Y chart and insert a linear trendline. We find that the linear trendline does not in any way match the obvious trend in the data points which we observe on the chart. They have a clear curved trend in a negative direction - values of y fall with rising values of x.
|
|
|
Experimenting with the different forms of trendline we find that the Exponential type produces a good fit to the data. The Logarithmic type is not available but if we convert the scale on the Y axis to Logarithms we get a good straight line pattern on the chart. We cannot draw a linear trendline through the roughly straight trend because there are now negative data elements in the series.
We find a similar relationship when we investigate radioactive decay. We could add trendlines in the same way as before.
In the next example, concerning world records for the mile, we see a clear relationship in the data we are given but we have strong reasons to suspect that a linear trend is misleading. If we follow the trend long enough we will eventually see a mile run in no time at all! To find the predicted mile records we select the data for the year and the time and drag the copy marker into the rows underneath. Rather than linear the data will probably turn out to be a negative exponential with times for the mile improving by ever smaller amounts through time and the curve 'flattening out'.
In the final example we see that sometimes we need to adjust the point where the trendline intersects the Y axis of the chart.This charts show the rates at which different volumes of water cool after they have been heated. In the first chart the trendline suggests that zero cc of water would take about five minutes to cool!
We therefore format the trendline and choose the Set Intercept to 0 option on the Options tab.
This leads us to suspect that the relationship may not be linear after all and we try some of the other trendline options available to see if there is a better fit. To produce a log chart we cheat a little by adding a data pair of 0.1 and 0.1 above our own observations and we then make a new chart which includes these data. (There is no value for Log(0) so Excel will not draw a Logarithmic trendline which includes it.) We can then add a Logarithmic trendline which shows a new insight into the pattern in the data.
Use the Back Button to Return to the previous page