Trendlines are used to predict possible future trends based on the historical data available. Commonly used Trendlines for predicting future data are as below :
The type of data available with you will help determine the type of trendline to use. A trendline is most accurate when its R-squared value is equal or close to 1. (R-squared value is a a number from 0 to 1 that reveals how closely the estimated values for the trendline correspond to your actual data. This is also known as the coefficient of determination.) When you fit a trendline to your data, Excel automatically calculates its R-squared value. Here we will discuss all trendlines one by one.
Linear Trendline
A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate. In the following example, we have data of number of hours worked and corresponding amount paid. We see that data is linear in nature and a linear trendline best fits the plotted data (see R-sqaured value). However, if we add logarithmic trendline, it has different R-sqaured value and doesn't best fit the data.
Logarithmic Trendline
A logarithmic trendline is a best-fit curved line that is most useful when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use negative and/or positive values.
Below example shows a logarithmic trendline to illustrate predicted population growth of animals in a fixed-space area. Population adrupltly increaes in start but after some time it gets leveled out as space for animals get decreased. The R-squared value is 0.9686, which is a relatively good fit of the line to the data.
Polynomial Trendline
A polynomial trendline is a curved line that is used when data fluctuates. It is useful for analyzing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three.
The following example shows an Order 4 polynomial trendline (two hills and one valley) to illustrate the relationship between date and stock price of a company. Notice that the R-squared value is 0.8272, which seem a good fit of the line to the data.
Power Trendline
A power trendline is a curved line that is best used with data sets that compare measurements that increase at a specific rate. This always shows a positively increasing data. You cannot create a power trendline if your data contains zero or negative values.
Exponential Trendline
An exponential trendline is a curved line that is most useful when data values rise or fall at increasingly higher rates. In below example, an exponential trendline has been used to illustrate the increasing acceleration of a car with respect to time. Note that the R-squared value is 0.9656, which means the line has good fit for the data.
Moving Average Trendline
A moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly. A moving average trendline uses a specific number of data points (set by thePeriodoption), averages them, and uses the average value as a point in the trendline. IfPeriodis set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.
In the following example, a moving average trendline of Period 2 shows a pattern in number of challans with respect to date .
