Trendlines

A trendline is an additional line that indicates the slope (or trend) in a particular data series and is also known as a line of best fit.
Trend lines can be helpful when you are analysing data because they can forecast future values based upon your current data.
A trendline is a line of best fit
Trendlines are often used for regressions analysis (or curve fitting).
You can also use Moving Averages to smooth out the fluctuations in a data series.


Which Chart Types ?

A trendline can be added to a data series in the following chart types: xy scatter, area, bar, column, line, stock, bubble
A trendline cannot be added to data series in a stacked, 3D, radar, pie, surface or doughnut chart.
If you change the chart type to a chart types that does not support trendlines then the trendline will not be displayed (deleted ?)
SS example trendline area


For line data when you do not need a chart you can use the AutoFill or one of the stats functions GROWTH or TREND to create data for best-fit linear or exponential lines.


Different Types of Trendlines ?

When you want to add a trendline to your chart there are 6 different trend types to choose from.
SS
Forecasting data using a trend line though will only be accurate if you choose the correct type of trend line.
Depending on the type of data will depend on which trend type is most appropriate.
A trendline is most appropriate when its R-squared value is at or near to 1.
When you add a trendline to a chart Excel automatically calculates the R-squared value.

LinearCreates a best fit straight line that shows how values in a data series increase or decrease at a steady rate.
Logarithmic 
PolynomialYou must also specify the order
Power 
Exponential 
Moving AverageYou must also select the number of periods


What type of Data

A trendline is not always appropriate. It all depends on the type of data.
Data that is appropriate for a trend line would be:
Paired umbers which can be plotted on an XY Scatter
Charts that have a Date category values (assuming no gaps)
It is possible to add a trendline to a chart that has a Text category axis but it just wont mean anything
A trendline is always associated with a particular data series
It is possible for one data series to contain multiple trend lines


Regression Analysis

Regression Analysis is a form of statistical analysis which is used for forecasting.
Regression Analysis estimates the relationship between variables so that a given variable can be predicted from one or more other variables.
By using regression analysis you can extend a trendline on a chart beyond the actual data to rpedict future values.
Interpolation is a specific case of curve fitting in which the function must go through "all" the data points.


R-Squared Value

include the equation
The R-sqaured value is the square of the correlation coefficient
Most statistical tests show the correlation coefficent as "r", but Excel displays it as "R".
This coefficient gives us a measure of the reliability of the linear relationship between the x and y values. Values close to 1 indicate excellent linear reliability.
The R-squared value that you can display with a trendline is not an adjusted R-squared value
For logarithmic, power and exponential trendlines Excel uses a transformed regression model.
To display more or less decimal places on the equation or R squared value when displaying on a chart, select the box and press the Increase Decimal or Decrease Decimal buttons


Important

Interpolation is the method of constructing new data points when you have a discrete set of known points.
You can quickly remove a trendline by right clicking on it and selecting Clear (or Delete key).


- Trendline Coefficients and Regression Analysis



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext