Trend line
Charts are often expected to present the current state and projection of future events. Nobody reliably knows what will happen in the future, but by approximating past data, it is possible to identify trends. Trend, by definition, represents the general direction of development or change. Excel sets trends and displays them using the trend line by application of various mathematical functions.
To add a trend line, we first need to make a chart based on the desired data set. After that, the trend line can be added in several ways. First of all, this can be done from the Design ribbon, by choosing Add Chart Element / Trendline, and then by selecting a sort of trend line. Further, it seems to me easier, is to right-click the mouse over the data series for which we want to present a trend line and then, from a context menu, choose Add Trendline option. On the right side Format Trendline panel will appear, which contains multitude of options for formatting the trend.
Excel calculates the trend based on several mathematical functions. Here’s a description of these functions, and what type of data is best to apply to one of them:
Exponential – approximation with exponential function is performed when the data series increases or decreases with increasing acceleration in each subsequent data.
Linear – such approximation is best applied when we have a series of data that behave linearly: decreasing or increasing. The general form of the approximation equation is y = ax + b.
Logarhytmic approximation is used when we have a series of data that is first fast growing and then growing slower and slower.
Polynomial approximation is used when we have a data series that fluctuates. By clicking Order we can change the order of equations, which changes the general form of function. For example, the general form of the equation for the second order approximation is y = ax2 + bx + c, while for the third order approximation y = ax3 + bx2 + cx + d etc. Also, here is possible with the Set Intercept option to influence the value of the last member of equation.
Power function is used for approximation when we have data in a series that grow at a regular rate.
Moving Average – Approaching the moving average we approximate data that tend to group more pronounced than trend tendencies. By clicking on the Period we can choose how many first data in the series are taken into account for calculating a prospect, based on which approximation is made later.
If we are curious, by clicking on the optional field at the bottom of the Display Equation on chart menu, we can see which function is being used for the approximation of the data. Also, by clicking the Display R-squared value on the chart option, we can show the coefficient of regression. The more the value of R2 is closer to the 1, the chosen function better describes the trend. This can be used as a validity of the trend line. That is, if the value shown is unlikely we can try to approximate the data with another function.
By entering the values of the Forecast, we make a prediction of the Forward and Backward movements by shifting the series in the graph and showing the direction of the trend in the past or, more frequently, for the future period. Finally, by clicking Trendline Name, we can select an automatic name for the trend line, or we can enter a custom name.