Newton Excel Bach, not (just) an Excel Blog

The LinEstGap user defined function (UDF) allows the built-in Linest function to be used on data with empty cells or hidden rows, returning results that agree with the chart trend-line function, which automatically ignores blank cells and hidden data.  It also optionally allows any lines with Error values to be ignored, rather than returning an error.

Following a comment here I have modified the code and added additional functions to work with all the non-linear curve  types provided by the chart trend line (Exponential, Logarithmic, Polynomial, and Power).

The revised spreadsheet includes examples and full open-source code, and can be downloaded from:

Linest-PolyDA.zip

The download zip file contains two versions of the spreadsheet.  Linest-PolyDA uses the new dynamic array feature.  Linest-PolyA has fixed size array functions that will work with older versions of Excel (see Using Array Formulas for more details).

I have extracted the code for extracting blanks and errors into a separate function (RemoveGaps), so the LinEstGap function is now reduced to:

Function LinEstGap(Ycells As Range, XA As Variant, Optional Const0 As Boolean = True, Optional Stats As Boolean = False, _
    Optional IgnoreErrors = False)
    Dim iErr As Variant
    Dim YA2() As Double, XA2() As Double

    iErr = RemoveGaps(Ycells, XA, XA2, YA2, Const0, Stats, IgnoreErrors)

    If iErr = 0 Then
        LinEstGap = WorksheetFunction.LinEst(YA2, XA2, Const0, Stats)
    Else
        LinEstGap = iErr
    End If
End Function

I have also added separate functions that call the RemoveGaps function, then Linest with modified X and Y data to return parameters for non-linear curves (see Using LINEST for non-linear curve fitting for more information on using Linest, and the download file for the VBA code).  Examples of the new functions are shown below (click on any image for a full size view):

The PolyEstGap function will return parameters for any order of polynomial function (but see Fitting high order polynomials for warnings on using orders greater than 3 or 4).

The functions ExpEstGap, PowerEstGap, and LogEstGap all return a two element array with values a and b for the functions:

• ExpEstGap:   y = b. e^a
• PowerEstGap; y = b.x^a
• LogEstGap:     y = a.ln(x) + b

The screen shots below show the PolyEstGap function used on data with gaps and errors and filtered data, but the other Gap functions all work the same way.

Using the built in LinEst on data with gaps returns an error, but the Gap functions return the same results as the chart trend line; i.e. any line with a gap in the data is ignored:

Filtered data is also ignored:

The chart trend line treats error values as zero, so returns invalid results.  The Gap functions return an error by default (line 19 below), but if the final optional argument ( IgnoreErrors) is set to True they will return the correct results for the data without the error lines.  Note that the results with errors in rows 12 and 13 below are exactly the same as those with rows 12 and 13 hidden above.

The Gap functions may also be used on data arranged in rows as shown below:

For the polynomial examples above the LinEstGap function may also be used with modified input, or with multiple columns of x data, to generate the same results. For the other non-linear curve types however modifying the LinEstGap input data will generate an error, but the new Gap functions return the correct results.