Non-Linear Trendlines

Non-linear Trendlines

The example below shows the sale prices of six cars, all of the same model but of differing ages. Despite the very high value of r², a close examination of the scatterplot reveals that the relationship is actually non-linear.

Age	Price	Predicted	Residual
3	3250	3100	150
4	2650	2660	-10
5	2100	2220	-120
6	1650	1780	-130
7	1250	1340	-90
8	1100	900	200

Intercept	4420
Slope	-440
Correlation	-0.9853
r-squared	97%

In Excel it is possible to fit the following non-linear trendlines to this data set.

This is a quadratic curve (or polynomial of order 2) - notice the x² term in the equation. It is clearly a very good fit. However, quadratic curves follow a U shape, so as age increases further the curve will eventually begin to rise again. Whilst the value of certain collector's cars might increase with age, clearly it is not a sensible pattern for most ordinary cars.

This is a power curve. Despite the high value of r² it is not a very good fit. More to the point, as x approaches zero the value of y will become infinitely large. New cars may be expensive but that is ridiculous!

This is a logarithmic curve - "Ln" is an abbreviation for natural logarithms or logarithms to the base e. This seems a good fit but, just like the power curve it goes off to infinity as x approaches zero.

This is an exponential curve. It offers us both a good fit and a sensible pattern. As age increases, the curve will fall gradually but never actually reaches zero. This seems sensible as any car, however old, probably has a small scrap value.

Use the spreadsheet to work through an exercise on fitting trendlines in Excel.

You have now completed the recommended route through this unit on Regression and Correlation. Make sure you visit our Links page to find out about other online resources relevant to this topic.