In this case the exponential fit has given almost exactly the same results as the linear fit, but in other cases there will be a much greater difference, as will be seen below. Which gives exactly the same result in a single step. The second formulation may conveniently be used in the Trend function: Alternatively (and more conveniently) the “b1” coefficient may be used directly in: For interpolation the “a” and “b2” factors are entered in:Īs shown above. To find the initial factor (1288.886) the “b” coefficient is entered in the Exp function. The resulting “a” coefficient is exactly equal to the power coefficient returned by the chart trend line results for an exponential curve. In this example the Y values in the Linest function have been replaced with their natural logarithm (using the LN function). The Linest and Trend functions will also work with non-linear data: The Trend function combines both steps in a single function, and returns exactly the same end result: Where a, X, and b are the cell addresses containing the appropriate values. To calculate the Y value for any given X enter the formula:
Note that the function is entered in a single cell, and must be entered as an array function to display both results: The Linest function returns the slope and Y intercept of the straight line that most closely fits the data (values a and b above). These two ranges have been named X_1 and Y_1, and the range names are used in all the examples: As in all the examples, the data consists of the columns of X data (A5:A7) and Y data (B5:B7). The simplest approach is to treat the data as linear, and use the Linest function as documented in the Excel help. I have treated this question as simply one of fitting different functions to a given data set in this case just three points, but the same procedures apply to any size of data. Please note that I do not know the details of how futures are priced. The data used in the examples is three points relating to the value of a futures contract.
The spreadsheet used in this post, and the original post, can be downloaded from Linest-poly-example.xls and Linest-poly.xls. It also provides some examples of using the related TREND function, and provides some general hints and warnings about using curve-fitting on a limited data set, particularly for the purposes of extrapolation.
Linear regression excel 2015 how to#
This post (in response to a recent question) provides some more detailed guidance on how to apply the function and use the results. Since I wrote Using LINEST for non-linear curve fitting in 2011 it has been by far the most popular post on this blog.
0 kommentar(er)
