Use the dialog shown below to insert a trendline that is the function that fits a point series best. A trendline is a function that shows a trend in a series of points, i.e. a trendline is the curve of bets fit of a specific type for a series of points. The trendline is added as an ordinary function. To create a trendline, you select the point series you want to base the trendline on and use → .
If the point series has custom Y-errors defined, these values are used to weight the points. The weight for each point is 1/σ2 where σ is the Y-error for the point. X-errors are not used.
You may choose between the following built-in functions. These functions will give an accurate result. For Linear, Polynomial and Exponential trendlines, you may select the Intercept field and specify the point where you want the trendline to meet the y-axis.
- Linear
This is a straight line with the function f(x) = a*x+b, where a and b are constants calculated so the line is the best fit to the point series.
The trendline is calculated so the sum of squares (SSQ) Σ(yi-f(xi))2 will be as small as possible. If possible the function will cross the points in the series; else the function will be so close to the series that the summation cannot get any smaller.
- Logarithmic
A logarithmic line of best fit is given as f(x) = a*ln(x)+b, where
aandbare constants, andlnis the natural logarithm function. To add a logarithmic function, no point in the series may have an x-coordinate that is negative or zero.A logarithmic function is a straight line in a semilogarithmic coordinate system. The point series is therefore converted to a semilogarithmic coordinate system and the logarithmic function with the smallest sum of squares (SSQ) is found.
- Polynomial
A polynomial is a function given by f(x) = an*xn + ... + a3*x3 + a2*x2 + a1*x + a0, where
a0...anare constants.nis the order of the polynomial. You need at least one more point than the order.- Power
A power function is given by f(x) = a*xb, where
aandbare constants calculated so the function is the best fit of the point series. To add a power function, no point in the series may have an x- or y-coordinate that is negative or zero.A power function is a straight line in a double logarithmic coordinate system. The point series is therefore converted to a double logarithmic coordinate system and the power function with the smallest sum of squares (SSQ) is found.
- Exponential
An exponential function is given by f(x) = a*bx, where
aandbare constants calculated so the function is the best fit of the point series. To add an exponential function, no point in the series may have a y-coordinate that is negative or zero.An Exponential function is a straight line in a semilogarithmic coordinate system with the y-axis as the logarithmic axis. The point series is therefore converted to a semilogarithmic coordinate system and the exponential function with the smallest sum of squares (SSQ) is found.
- Moving average
Moving average is a series of straight lines based on the average of the previous points. The Period determines how many points are used for the average. If Period is 1 only one point is used, which actually isn't an average. This will draw a line directly between the points. When Period is larger than 1, the y-coordinate for the line at each point will not be the same as the y-coordinate of the point. Instead it will be an average of the previous points.
In this tab you can enter your own trendline models. The model is entered as a standard function, where all the constants that you want Graph to find are named with a $ followed by any combination of characters (a-z) and digits (0-9). Examples of valid constants are: $a, $y0, $const.
An example of a model could be f(x)=$a*x^$b+$c.
The program tries to calculate the constants $a, $b and $c
so that f(x) will be as close to the point series as possible.
You can use the button to add the model to the saved list with a name.
The program needs a guess for where to start the search for the optimum. As default the guess for all constants is 1, but this can be changed for models added to the list. A better guess will increase the possibility that an optimum can be found.
Graph will try to find the constants for the model f(x) so the sum of squares Σ(yi-f(xi))2 will be the smallest possible. The program will start with the guess and move towards the minimum of the sum of squares. If a solution is not found after 100 iterations or the given guess is not valid, the program gives up.
It is possible, even though it rarely happens, that more than one minimum exists. In this case the minimum nearest to the guess will be found, even though this may not be the best.
Notice that you should avoid redundant constants as they might confuse the program.
For example this model has a redundant constant: f(x)=$c+$d/($a*x+$b).
Notice the relation between the constants $a, $b and $d.
If you multiply $a, $b and $d with the same value the resulting function will not be changed.
This means that there are an infinite number of combinations of constants with the same resulting function and hence an infinite number of best solutions.
This may confuse the program when it tries to find the best one.
Therefore either $a, $b or $d should be removed.
When the trendline is added, the correlation coefficient R2 is shown in the comment. The closer R2 is to 1 the closer the trendline is to the points.