Using LINEST for non-linear curve fitting

Posted on 19/01/2011 by dougaj4

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.

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.

A 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.  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.  The chart trendlines have the options of: Linear, Exponential, Logarithmic, Polynomial (up to order 6), and Power.  There is also a “Moving Average” option, but this does not provide a trendline equation.  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.  Fortunately it is straightforward to get the trendline equations (and other statistics) for each of the chart trendline types using the LINEST worksheet function.

I have created a spreadsheet with examples of each trendline type, which may be downloaded here:

Linest-poly.xls

The functions used for linear and polynomial trendlines are shown in the screenshot below (click image for full size view):

Linear and Polynomial Trendlines

Note that:

  • The functions as displayed use named ranges (X_1 to X_3 and Y_1 to Y_3)
  • The functions are entered as array functions to display all the return values; i.e. enter the function in a cell, select that cell and sufficient adjacent cells to display all the required values, press F2, press Ctrl-Shift-Enter.
  • Alternatively the INDEX function may be used to return specific values; e.g. to return the b value from the linear example use =INDEX(LINEST(Y_1, X_1),2)
  • Higher order polynomial functions may be returned by simply adding to the list of powers in the curly brackets (but note that this is often not a good idea because of “over-fitting“)

Functions for exponential, power, and logarithmic trendlines are shown below:

Exponential, Power and Logarithmic Trendlines

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.  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 these examples note that:

  • Equations are in the form: y = a.e^bx (exponential), y = a.x^b (power) or y = b.ln(x) + a (logarithmic).  In each case in the examples the power factor (b) is shown in bold, and the constant term (a) is shown in bold and italic.
  • The LOGEST function returns an equation of the form y = a.b^x
  • The LINEST function will return exactly the same values if entered as =EXP(LINEST(LN(Yrange), XRange)), and this line is equivalent to the y = a.e^bx line returned by the chart.

Update 27 Jan 2011:

Coincidentally, Chandoo at Pointy Haired Dilbert is also running a series on estimating trend lines in Excel, which is well worth a look at: Are You Trendy

Posted in Charts, Excel, Maths, Newton | Tagged , , , , , | 96 Comments

Evaluating higher order polynomials …

Posted on 17/01/2011 by dougaj4

… and a better solution for quadratics.

Having written a better solver for high order polynomials, that raises a few other issues.  The error in the roots found by the RPolyJT was checked by simply substituting each root as the x value in: ax^n + bx^(n-1) + cx^(n-2) + …+e = 0.  The problem is that finding the difference between two similar large numbers significantly reduces the precision of the result.  A much better approach is known as the Horner Scheme, which uses Synthetic Division to arrive at the value of the polynomial without subtraction of the values raised to high powers.

This method has now been added to the Polynomial spreadsheet with the following changes:

  • RPolyJT has been modified to call the new function EvalErrorHC() to evaluate the error in the roots found.
  • EvalErrorHC may also be used as a UDF from the spreadsheet.
  • EvalPolyHC will evaluate any polynomial for a list of values of x (real or complex), which may take any value (i.e. not necessarily estimated roots).

For similar reasons, the standard quadratic formula will not give accurate results when b^2 and 4ac are large and of similar magnitude.  Better accuracy is given by a method included in the Fortran code used as the basis of the RPolyJT function.  The Quadratic function in the Polynomial spreadsheet has now been revised to use this method.

The revised spreadsheet may be downloaded (including full open source code) from:

Polynomial.zip

Results of the new RPolyJT and EvalErrorHC functions are shown in the screen shot below:

Click for full size view

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 4 Comments

Solving higher order polynomials

Posted on 13/01/2011 by dougaj4

The Quartic spreadsheet presented here previously (most recently here) uses algebraic techniques to provide an “exact” solution to polynomial equations of up to fouth order.  For polynomials of higher order there is no general algebraic solution, and numerical methods are required.  Also under some circumstances the “exact” solution, when evaluated with floating point numbers of limited precision, produces results that are very far from exact.  In these circumstances a well written iterative numerical procedure can give much better results.

One such procedure is the Jenkins-Traub algorithm, which apart from the advantage of one author having an excellent family name, is also said to be both fast and stable.  The Wikipedia article says that it “has been described as practically a standard in black-box polynomial root-finders”.

I have added a VBA user defined function (UDF) implementation of the Jenkins-Traub method to the Quartic spreadsheet, which has been re-named Polynomial.xlsb, and is available for free download, including open-source code. 

Download Polynomial.zip

The VBA code is a translation of Fortran77 code at the NetLib library.  Similar code in Fortran90 format can be found at Alan Miller’s Fortran Software.  Both of these sites are invaluable sources of open source scientific Fortran code.  The chosen routine provides all real and complex solutions to any polynomial with real coefficients.  Fortran routines are also available for polynomials with complex coefficients, but I have not yet translated these.

The screen-shots below show output from the Jenkins-Traub UDF (RPolyJT) for a quartic polynomial, followed by the results from the Quartic UDF for the same input.  Note that the Jenkins-Traub result is close to machine precision, but in this case the Quartic output has errors in the third decimal place.

RPolyJT output for quartic polynomial

Quartic output for the same polynomial

Output from RPolyJT for higher order polynomials is shown in the screenshots below, where the function has been used to solve the equation : 2.x^60 – 1 = 0.  The roots are 60 equally spaced points in the complex plane, with a modulus of 0.5^(1/60).  Note that the input is the range A26:A86; the cells containing zero coefficients could have been left blank, with the same results.  The top and bottom of the input and output results are shown in the two screenshots below:

Top of input and output for 60th order poynomial

Bottom of input and output for 60th order polynomial

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , , , | 12 Comments

LatPile update

Posted on 11/01/2011 by dougaj4

I have just posted an update to the LatpilePY spreadsheet, fixing a bug in the way in which it read the first row of data from input PY curves, which was resulting in the program failing to run if there were two or more PY curves specified.

The revised spreadsheet, including full open source code, can be downloaded from LatpilePY.zip

Posted in Concrete, Excel, Geotechnical Engineering, Newton, UDFs, VBA | Tagged , , , , , , | 2 Comments

Elegant Solutions – completing the square

Posted on 08/01/2011 by dougaj4

When I was at school (many years ago), if I recall correctly, we were told of the “completing the square” method of solving quadratic equations, but I never really appreciated the elegance of this method because it was treated as a purely algebraic process, with no reference to the corresponding geometry. To make things worse, we were told that the method would not be included in the all important exams, so we weren’t really listening.

I was reminded of this when reading Ian Stewart’s excellent book “Why Beauty is Truth” where he tells how this method was used by the ancient Babylonians about 3000 years ago, and describes the method in geometric terms, literally completing a square, which makes the result both obvious and elegant.

In brief, the method is as follows:

  1.  Start with an equation of the form: x² + bx = c, where b and c are given values, and we wish to find x.
  2. Draw a square of length x, representing the x² term.
  3. Draw two rectangles of sides x and b/2, representing the bx term
  4. Arrange the three shapes to form a chunky L shape (see spreadsheet below)
  5. “Complete the square” by adding an area of (b/2)² to both sides of the equation
  6. Take the square root of both sides, giving (x + b/2) = √(c + (b/2)²)
  7. Rearrange, giving x = √(c + (b/2)²) – b/2

This is presented in more detail in the spreadsheet below, in a slightly modified form to show the exact equivalence to the “quadratic formula” in its standard form.

The spreadsheet is “live” on sky drive, and should allow viewing in any browser. Click on the “full screen” icon in the bottom right hand corner to view full screen. The spreadsheet can only be edited by approved persons (currently only me), but anyone should be able to download it.

Posted in Excel, Maths, Newton | Tagged , , , , | 1 Comment