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 AL-Spline-Matrix07.zip and AL-Spline-Matrix03.zip. 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:

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:

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:

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.

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.

LikeLike

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:

https://newtonexcelbach.wordpress.com/2010/05/22/linking-alglib-c-to-excel-vba/

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.

LikeLike

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.

LikeLike

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

http://fastexcel.wordpress.com/2012/06/19/designing-the-speedtools-function-library-my-vba-to-c-journey-part-1/

“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)

LikeLike

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

Hello,

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.

Regards

Trevor

LikeLike

Trevor – I’m actually in the process of updating my links to ALGLIB, using the Python interface, so I’ll have a look at that next.

In the mean time you might be able to find the answer here: http://www.alglib.net/interpolation/fastrbf.php

LikeLike

Trevor – see today’s post:

https://newtonexcelbach.wordpress.com/2014/07/23/alglibpython-linear-and-non-linear-fitting-functions/

Please ask if anything is not clear.

LikeLike