Non linear regression – 2; ALGLIB functions

The previous post looked at using the Excel Solver to fit a non-linear equation to a given set of data, using the least squares method.  This approach is reasonably convenient and straightforward, but it has a number of disadvantages:

  • The Solver tends to be very slow, compared with alternative methods
  • The solution does not update automatically if the data changes
  • It is necessary to set up the calculation of function results and squares of the error values for each new application, or for alternative functions.
  • Control of the precision of the results is limited

The ALGLIB non-linear least squares functions provide better performance in all these respects, and with the VBA functions described below, are quick and simple to use.  Two new User Defined Functions (UDFs) have been added to the AL-Spline-Matrix spreadsheets.  The new spreadsheets (including full open-source code) can be downloaded from and  The two new UDFs are:

  • AL-NLFit – non-linear least squares fit for a function evaluated by a VBA routine.
  • AL-NLFitText – non-linear least squares fit with automatic evaluation of a function entered as text on the spreadsheet.

The ALGLIB routines follow an iterative process, requiring the evaluation of the functions, and also their partial derivatives with respect to each parameter.  Optionally, the Hessian of the function (a matrix of all second partial derivatives with respect to each parameter) may also be evaluated.  The spreadsheet UDFs allow for the derivatives and Hessian to be evaluated analytically, or they may be found automatically by a finite difference method.

Input and output for the two new functions are shown in the screen shots below:

AL_NLFit function input and output (click for full size view)

Al_NLFitText Function input and output

These functions have been applied to the fitting problem described in the previous post.  To use AL_NLFit it is necessary to write a VBA routine to evaluate the function and its partial derivatives:

Function CreepFD1(XA() As Double, CoeffA() As Double, RtnType As Long) As Variant
  Dim A As Double, B As Double, C As Double, D As Double, E As Double, F As Double, Slopea As Variant, X1 As Double, X2 As Double
    Dim Th As Double, T As Double

  A = CoeffA(0)
  B = CoeffA(1)
  C = CoeffA(2)
  D = CoeffA(3)

    Th = XA(0)
    T = XA(1)

  Select Case RtnType
    Case 1
  CreepFD1 = A * Log(B * T) - C * T - D * Th
    Case 2
  Slopea = GradientA("CreepFD1", CoeffA, XA, 4, 2)

  CreepFD1 = Slopea
    End Select
End Function

Note that to find the function value for the given values in XA and CoeffA (for RtnType = 1) the routine must include the function to be evaluated, but to calculate the array of partial derivatives (for RtnType = 2) these may either be calculated within the routine, or alternatively (as in the case shown) the GradientA function may be used, which recursively calls the evaluation function with small offsets to evaluate the derivatives by the finite difference method.

To use AL_NLFitText it is simply necessary to enter  the function as text on the spreadsheet, and optionally the functions for the partial derivatives immediately underneath.  When these are omitted the derivatives are automatically calculated by the finite difference method.

The input and results for the concrete shrinkage problem are shown in the screen shot below:

Input and results fitting concrete shrinkage data to two alternative formulas

It can be seen that:

  • AL_NLFit and AL_NLFitText have given almost identical solutions in both cases.
  • The ALGLIB solution for k1calc.1 is different to that found by the Solver method, with a lower average error, but the gradient of the curve becomes negative for high values of T, whereas the data always has a positive slope.
  • The solution for k.calc.2 has close to zero error, and (unlike the Solver solution) the calculated coefficients are identical to those used to generate the base data.

Log scaled plots of the two solutions are shown below:

k1calc.1 = A*LN(T*B)-C*T-D*Th (Log scale)

k1calc.2 = (A+B*exp(C*Th))*T^D/(T^E_ + F * Th) (Log scale)

In summary, the non-linear least squares UDFs provide a quick and flexible method of performing non-linear least squares regression, and with an appropriate base function provide accurate results.

This entry was posted in AlgLib, Concrete, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

8 Responses to Non linear regression – 2; ALGLIB functions

  1. Scott says:

    I have found your blog very useful and the add-ins clear and easy to use. I have been looking for an 2D RBF function for Excel for some time when I found ALGLIB and wondered how to expose those functions to Excel? I have little to no experience in compiling code and as you have managed to expose some of the ALGLIB functions I thought I would ask if you could either expose the RBF ones or post an idiots guide on how to do it? Any help would be appreciated, thanks.


    • dougaj4 says:

      Scott – The ALGLIB RBF functions are only included in the latest release of the package, which does not include a VBA version. This means that to link to Excel you would need to compile one of the current versions as a dll or xll and link to that with some VBA routines.

      The process for doing that with C++ is described here:

      There is a fair amount of work involved, but I did manage to get it working successfully with the C++ matrix routines, and I’m certainly not a C++ expert.

      It may be that the VB.Net routines are an easier approch for someone familiar with VBA, but I haven’t tried those.

      I would like to do some more work linking to the latest Alglib version, but I don’t know if/when I will fnd the time.


      • Scott says:

        Thanks for the information. I will have a look at your link and see if I can follow the process. I’ll let you know how I get on. Thanks again.


  2. dougaj4 says:

    Scott – you may also find the series of articles at Charles Williams’ Fast Excel Blog useful:

    “Designing the SpeedTools function library – my VBA to C Journey part 1
    This is the first of a series of posts about my perilous voyage from VBA to C++.”

    (Now up to Part 9)


  3. Pingback: Daily Download 14: Curve Fitting 1 | Newton Excel Bach, not (just) an Excel Blog

  4. Trevor says:


    Thank you for your useful blog.

    I am trying to use the Excel ALGLIB non linear regression functions as contained in your concrete creep problems. Do you know if there is a way to constrain the parameter ranges? Ie. If I wanted parameter “A” to never be < = 0 for example.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s