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:
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.
Newton Excel Bach, not (just) an Excel Blog 