Trend lines are very useful in observing the relationship between two variables as well as predicting future values. Trend lines are frequently used in simple linear regression to observe the relationship between two variables. The shape of the trend line explains the type of the relationship between the variables. For example, in the case of simple linear regression, the trend line is a straight line, which is represented by the mathematical equation of a straight line: y = mx + c.
Let's use the sample file Sample – Coffee Chain (Access). Open a new worksheet and select Sample – Coffee Chain (Access) as the data source.
Once the data is loaded, perform the following steps to add a trend line to a plot:
- Click on the Show Me button to bring the Show Me toolbar on the screen.
- Select Profit and Total Expenses from Measures by pressing the Ctrl key and clicking on both the fields.
- Click on the scatter plots button from the Show Me toolbar.
- Click on the Analysis menu option and uncheck the Aggregate Measures option.
- Right-click on any data marker or anywhere in the plot area and click on the Show Trend Lines option in Trend Lines to see a plot with a linear trend line, as shown in the following screenshot:
The trend line that is added by default is a linear trend line, which is the simplest type of trend line explaining the relationship between two variables, and as we would expect in this recipe, the relationship is quite linear; that is, the profits are generally higher for expensive products. This trend line is mathematically represented as Profit = 0.628675*Total Expenses + 27.1093. This trend line can be edited to observe complex relationships, such as logarithmic, exponential, and polynomial. To change the trend line, right-click on the trend line and click on Edit Trend Line. You'll see a dialog box with various options, as shown in the following screenshot. From this box, select options from Model type to observe which trend line fits the data better:
When we fit a linear model trend line on the data, we are essentially performing linear regression to fit a straight line to the data. If you are unfamiliar with linear regression, you will find the following resources helpful:
- The Khan academy video at http://youtu.be/OhUkMQtBGmE
- A reference from a Psychology course at Illinois State University: http://psychology.illinoisstate.edu/jccutti/psych340/fall02/oldlecturefiles/regression.html