Non-linear fitting with LINEST

The LINEST worksheet function can be used to carry out non-linear data fitting. I thought it was worth writing something about this because I didn't find Excel's help particularly helpful.

Say you want to obtain a values for ke, kq and ks from the following relationship:

sd2 = ke2 + kq2.p + ks2.p2

where sd is the standard deviation of pixel values in an image with mean pixel value p. This example is used during noise component analysis of x-ray images from a CR or DR system. If the images have been linearised then the mean pixel value p is the same as the radiation dose received by the detector.

What is needed for LINEST to carry out a fit is three columns that contain the values of sd2 (say cells A1:A10), p (cells B1:B10) and p2 (cells C1:C10).

LINEST can then be run in combination with the INDEX command using the following syntax:

=INDEX( LINEST( A1:A10, B1:C10, TRUE, TRUE ), 1, 1 )

The first range passed to LINEST is the sd2 values. The next is a range that includes both p and p2. The first TRUE ensures that the fit constant is calculated, rather than being forced to zero. The second TRUE tells LINEST to return the full fit statistics as an array, rather than just the gradient coefficients and intercept.

The INDEX command that surrounds LINEST is there to access specific elements of the data array that LINEST returns. The first 1 tells INDEX to look at the first row of data. The second 1 says to look at the first column of that row. This corresponds to the coefficient of the highest power that has been asked for, so in our case this is ks2.

The other two coefficients can be obtained using:

=INDEX( LINEST( A1:A10, B1:C10, TRUE, TRUE ), 1, 2 ) for kq2

=INDEX( LINEST( A1:A10, B1:C10, TRUE, TRUE ), 1, 3 ) for ke2

The actual coefficients of fit can be obtained by taking the square root of these values.

The data in the first column of the third row is the r2 coefficient of the fit:

=INDEX( LINEST( A1:A10, B1:C10, TRUE, TRUE ), 3, 1 )