Equations are in the form: y = a.e^bx (exponential), y = a.x^b (power) or y = b.ln(x) + a (logarithmic).
For these lines it is possible to use either the LINEST function, or the LOGEST function, but since LOGEST simply calls LINEST internally, and provides little if any extra convenience, it does not seem to provide much value. In this case the process is not quite so straightforward, because in most cases one or both of the values returned by the function must be modified to give the values shown in the chart trend lines. The functions used for linear and polynomial trendlines are shown in the screenshot below (click image for full size view):Įxponential, Power and Logarithmic Trendlines I have created a spreadsheet with examples of each trendline type, which may be downloaded here: Fortunately it is straightforward to get the trendline equations (and other statistics) for each of the chart trendline types using the LINEST worksheet function. The chart trendline solution is OK if what you want to do is display the trendline equation on a chart, but if you want to use the numbers in some further analysis, or even just display them elsewhere in the spreadsheet, or copy them to another document, it is far from convenient. There is also a “Moving Average” option, but this does not provide a trendline equation. The chart trendlines have the options of: Linear, Exponential, Logarithmic, Polynomial (up to order 6), and Power. The most frequent answer is to plot the data on an XY (“scatter”) chart, and then use the “Fit Trendline” option, with the “display equation on chart” box checked. Update 28 June 2015: Also see Using Linest for non-linear curve fitting examples, hints, and warnings for more examples of fitting exponential and polynomial curves using LinEst.Ī frequent question on internet forums everywhere is how to do a least squares fit of a non-linear trend line to a set of data. Update 14 March 2020: See LinEstGap with non-linear functions for the latest version of Linest-Poly with new functions for non-linear curves allowing more convenient input and work with data with gaps errors, and/or hidden lines.
0 kommentar(er)
